SQL statement for a 13 character long timestamp

Question

I have a closed source application which put a 13 character long timestamp in a MySQL database. One value is for example:

1277953190942

Now I have the problem, that I have to write a sql statement which will return me all results of a table which match a special day.

So I have for example 01. July 2010 and I will get all rows where the time is in between 01. July 2010 00:00:00 until 01. July 23:59:59.

How do I have to write this sql statement?

select * from myTable where TIMESTAMP = ???

Does anyone know this?

Best Regards.

Answer 1

This is a Unix timestamp with millisecond precision, i.e. the number of milliseconds since Unix epoch. Hence the correct statement would be

select * from myTable where DATE(FROM_UNIXTIME(timestamp / 1000)) = DATE('2010-07-01');

Note that the query won't be fast es every value in your table will have to be converted to a date for comparison. As soon as this query starts to make problems (and not a second earlier), you may want to use an approach using 2 timestamps for the beginning and end of day which wouldn't require much conversion overhead. I'll leave this as an exercise to the eager though ;)

Answer 2

SELECT * FROM your_table
 WHERE DATE( FROM_UNIXTIME( timestamp /1000 ) ) between '2010-07-01 00:00:00' and '2010-07-01 23:59:59'

Answer 3

Look at http://en.wikipedia.org/wiki/Unix_epoch.

This number is probably the number of milliseconds since Jan 1, 1970.