SQL statement for a 13 character long timestamp

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 mat开发者_Python百科ch 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.

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

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

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

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

The timestamp contains the ms :/ if you look at the time now 127 902 676 7 and the time the you have 127 795 319 0 942 you will see that it consists bar the last 4 integers of 0942 i.e .9ms, you need to convert it to a UNIX Timestamp from epoch

Depending on if your using PHP, here is a little function for you

<?php
function EpochToUnix($t)
{
        return mktime(substr($t,8,2),substr($t,10,2),ubstr($t,12,2),substr($t,4,2),substr($t,6,2),substr($t,0,4));
}
?>

But you can see wqhat you have to do