Support Questions
Find answers, ask questions, and share your expertise

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

Solved Go to solution
Highlighted

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

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.

View solution in original post

5 REPLIES 5
Highlighted

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

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.

View solution in original post

Highlighted

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

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

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

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.

Highlighted

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

@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');

Highlighted

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

@Sooraj Antony

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

Don't have an account?