Geek Answers Handbook

SQL statement for a 13-character timestamp

I have a closed source application that puts a 13-character chronic label into a MySQL database. For example, one value:

1277953190942

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

So, I have an example 01. July 2010, and I will get all the lines where the time is between 01. July 2010 00:00:00 to 01. July 23:59:59.

How do I write this sql statement?

select * from myTable where TIMESTAMP = ???

Does anyone know this?

Regards.

+5
source share
4 answers

Unix timestamp , Unix . ,

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

, , . ( ), , . , ;)

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

The timestamp contains ms: / if you look at the time 127 902 676 7and time at which you have it 127 795 319 0 942, you will see that it consists of the last four integers 0942ie.9ms, you need to convert it to a UNIX timestamp from the era

Depending on whether you use PHP, here's a little function

<?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 what you have to do

0
source

More articles:


All Articles