UNIX_TIMESTAMP function returns different values when provided with hardcoded time and the one fetched from table

[#1]

The below query fetches UNIX_TIMESTAMP of the same time string but one of them is hardcoded and other fetched from a table.

select distinct UNIX_TIMESTAMP(TIME, 'yyyy-mm-dd HH:mm:ss'),UNIX_TIMESTAMP('2015-08-22 00:00:32', 'yyyy-mm-dd HH:mm:ss') from clicks where time='2015-08-22 00:00:32';

Both the fields are supposed to give the same result as the time string is same. But the output is,

_c0 _c1

1440201632 1421884832

Is there any reason why it differs? Is there any workaround?

[#2 · Accepted solution]

Hi @Sooraj Antony. Here is the issue - your format string is incorrect. Be careful - the format string is CaSe SenSiTive. 😉

Here is documentation: http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

Here is an example - note the case sensitivity in the format:

select  
     UNIX_TIMESTAMP('2015-08-22 00:00:32', 'yyyy-MM-dd HH:mm:ss'),
     from_unixtime(1440201632, 'yyyy-MM-dd HH:mm:ss'),
     from_unixtime(1421884832, 'yyyy-MM-dd HH:mm:ss')
from sample_07 
limit 1; 

Results:

col1       col2                col3 
1440201632 2015-08-22 00:00:32 2015-01-22 00:00:32

I hope this helps.

[#3]

Thats perfect!!! But still just curious to know how it works fine when the string is taken from a table and "mm" in format.

[#4]

That's the interesting part - it actually wasn't working correctly. It was just hard to see the issue without doing the corresponding from_unixtime call.

When you were doing the UNIX_TIMESTAMP call it was using '00' as the month, since the format string was using 'minutes' instead of 'months'. For whatever reason, the UNIX_TIMESTAMP function returns a timestamp value when the format string causes you to pass in invalid data like Month = '00'. In my opinion it should fail instead of returning invalid data like that.

Check out this query - the first 2 columns use the correct data format string, while the second 2 columns use the invalid date format string (minutes instead of months):

select  
     from_unixtime(1440201632, 'yyyy-MM-dd HH:mm:ss') as `good_date1`,
     from_unixtime(1421884832, 'yyyy-MM-dd HH:mm:ss') as `good_date2`,
     from_unixtime(1440201632, 'yyyy-mm-dd HH:mm:ss') as `bad_date1`,
     from_unixtime(1421884832, 'yyyy-mm-dd HH:mm:ss') as `bad_date2`
from sample_07 limit 1; 

and results:

good_date1           good_date2           bad_date1            bad_date2
2015-08-22 00:00:32  2015-01-22 00:00:32  2015-00-22 00:00:32  2015-00-22 00:00:32

Notice that the bad dates have zeros in their month field instead of 8 and 1 respectively.

Hope this helps.

[#5]

@Sooraj Antony

m is used for minute

M is used for month

In your case should be YYYY-MM-DD hh:mm:ss or yyyy-MM-dd hh:mm:ss

select UNIX_TIMESTAMP('2000-01-01 10:20:30','yyyy-MM-dd hh:mm:ss');

[#6]

@Sooraj Antony

@bpreachuk response is appropriate. Please accept or vote to appreciate the effort.