ansaurus

Question

SQL statement for a 13 character long timestamp

Answer 1

+1 A:

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

dcp 2010-07-13 12:11:05

a 13 digit timestamp contains milliseconds, otherwise it would be 40 * 1000 years from epoch, thus around 42010 ;)

sfussenegger 2010-07-13 12:27:45

Thanks, corrected it.

dcp 2010-07-13 12:38:05

Answer 2

+1 A:

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

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

Darryl Peterson 2010-07-13 12:11:29

I would say that aswell

RobertPitt 2010-07-13 12:26:33

Answer 3

+3 A:

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

sfussenegger 2010-07-13 12:14:31

Thanks a lot! One more question: Is it possible that I can create a sql statement in which I have several dates? For example I need the rows for date1 = '2010-07-01', date2 = '2010-07-02', date3 = '2010-07-03', ... Of course I can do x times the sql statement, but perhaps I can do one statement for all different dates?

Tim 2010-07-13 13:53:19

If I understand correctly, you want to do something like `where DATE(FROM_UNIXTIME(timestamp / 1000)) IN (DATE('2010-07-01'), DATE('2010-07-05'), DATE('2010-07-08'))`. This should work. Same for the BETWEEN operator (see http://dev.mysql.com/doc/refman/5.1/de/comparison-operators.html). I'm short on time, so I can't validate my claim right now. It should be pretty trivial though. Cheers

sfussenegger 2010-07-13 17:08:41

No, that is not what I want. If I do a "in (Date(...), ...)" I get one(!) result row. But I want to have one result row per Date.

Tim 2010-07-13 19:46:34

well, you should get every matching row once, as in every SQL query. But I'm still not sure I understand your requirement. Could you elaborate a bit?

sfussenegger 2010-07-14 08:35:58

With this statement I get always one row (because I have a more complex statement with sum(), etc.):select * from myTable where DATE(FROM_UNIXTIME(timestamp / 1000)) = DATE('2010-07-01');But I have to get one row per date. So I have the dates 2010-07-01, 2010-07-02, 2010-07-03 and for every date I need one result row and not one row for all dates together like it is done wit the IN () list.

Tim 2010-07-14 08:45:02

I created a new post, because it is another topic:http://stackoverflow.com/questions/3245532/mysql-list-of-sum-for-several-conditions-in-one-sql-statement

Tim 2010-07-14 11:17:20

Answer 4

A:

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

RobertPitt 2010-07-13 12:24:55

ansaurus

tags:

views:

answers:

SQL statement for a 13 character long timestamp

related questions