axon axon - 7 months ago 14
SQL Question

How to compare UNIX DATA and TIME

I have: (this example works)

SELECT *
FROM messages m
WHERE
m.create_timestamp > STR_TO_DATE('2016-01-28 05:06:38', '%Y-%m-%d %H:%i:%s')
ORDER BY m.create_timestamp
LIMIT 100;


but I have this '2016-01-28 05:06:38' datetime in UNIX FORMAT.

I tried:

m.create_timestamp > FROM_UNIXTIME(1453946798, '%Y-%m-%d %H:%i:%s')


But this doesn't work.

I want to compare date in UNIX FORMAT:
m.create_timestamp > 1453946798
.

Help please!

Answer

UNIX format is just the number of milliseconds since 1970-01-01 00:00:00 UTC. So you can just compare the values between them. The highest value will be the point that is later in time.

A simple:

m.create_timestamp > 1453946798
  • > 1453946798 means it's after 2016-01-28 05:06:38
  • < 1453946798 means it's before 2016-01-28 05:06:38

Make sure the unix timestamps have the same precision though. Sometimes it's the number of seconds since epoch instead of milliseconds for precision and storage size reasons.

If you have a timestamp in milliseconds to compare with your 1453946798, divide it by 1000 and floor it before comparing the timestamps.


To convert a DATETIME to a TIMESTAMP, you can use the following:

UNIX_TIMESTAMP('2015-01-15 12:00:00');

Of course, replace '2015-01-15 12:00:00' with the date to convert.