4

I have a query selecting the greatest value of 2 timestamps.

To achieve it I'm calling:

SELECT GREATEST( IFNULL( tableb.create_time, 0 ) , IFNULL( tablea.create_time, 0 ) )

I'm using IFNULL because the create_time value can be null. The problem is that the result is something like '2014-0' instead of '2014-08-14 17:04:39'

Here's a fiddle to illustrate my problem

The result is OK when:

  • I'm not using IFNULL
  • I'm using IFNULL but I have no join in the query (so the GREATEST is performed on 2 timestamps in the same table)

So for me the problem comes really from the use of GREATEST and IFNULL in a join query.

2
  • Use TIMESTAMP'19700101' instead of 0. Aug 14 '14 at 22:54
  • 1
    Nice! Using COALESCE instead of IFNULL is also solving the issue. I you want create an answer so I can mark it as solving my question!
    – darkheir
    Aug 14 '14 at 22:57
1
SELECT
    GREATEST
    (
        IFNULL(tableb.create_time,FROM_UNIXTIME(18000)),
        IFNULL(tablea.create_time,FROM_UNIXTIME(18000))
    );

It just so happens that FROM_UNIXTIME(18000) is '1970-01-01 00:00:00'

1
  • "It just so happens that FROM_UNIXTIME(18000) is '1970-01-01 00:00:00'" – doesn't it depend on @@session.time_zone? If you set it to '+00:00', for instance, FROM_UNIXTIME(18000) will start evaluating to '1970-01-01 05:00:00', no?
    – Andriy M
    Dec 16 '15 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.