is it possible to compare a mysql TIMESTAMP to a millisecond?

Question

For reasons that are too obscure to get into, I have a millisecond representation of a specific time and I have a mysql database filled with mySql Timestamps and I'm curious if it's possible to just do native comparisons in sql such as select * from myTable where time_stamp_column > 1264665600000; or something along those lines.

I've been running some tests and the results are pretty strange. It doesn't complain but returns row that don't fit the criteria.

Thanks in advance.

[EDIT] Ok if using milliseconds in mySql is a non-starter, what's the best way to compare the dates, assuming I'm starting out in millis and am in java.

Answer 1

You don't get millisecond-accuracy, but judging by the zeroes at the end of your example, you may not need it.

Use FROM_UNIXTIME to convert a Unix timestamp to a MySQL TIMESTAMP. It looks like you have milliseconds since Unix epoch, so just divide them by 1000 to convert:

SELECT * FROM myTable WHERE time_stamp_column > FROM_UNIXTIME(1264665600000/1000);

You may have to adjust for timezone/DST issues here since the SQL timestamps are, utterly depressingly, local time.

Answer 2

Apparently this is a known bug: Bug 8523. See Once upon a timestamp(milliseconds)...

In fairness sake, MySQL have indeed supplied a way to retain milli and micro seconds in a decimal field DECIMAL(17,3), and it is also queryable as if it were a timestamp BUT why isn’t it possible to store in a DATETIME or TIMESTAMP field?