ansaurus

Question

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

Answer 1

+3 A:

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?

eed3si9n 2010-01-29 23:40:39

Answer 2

+2 A:

No, TIMESTAMP and DATETIME columns do not store values with millisecond accuracy unfortunately. A work-around for this is to use a BIGINT column, and have your timestamps with the appropriate number of millisecond places, ie. 1264808431000. Unfortunately, this means any comparison logic in your application or in MySQL will have to be on a BIGINT basis.

zombat 2010-01-29 23:41:52

I don't find this a drawback at all, personally. Integers are way simpler and more uniform across databases and data access layers than the mess of date types. It may not be ideologically pure, but there is no practical downside; it's INT/BIGINT timestamps for me, every time.

bobince 2010-01-30 00:00:27

@bobince - I find it a bit unwieldy at times, and it does make it harder to use some of the RDBMS-specific date functions. On the other hand, sometimes you can save a byte or two on a record using INTs. It's a fairly contextual decision.

zombat 2010-01-30 00:29:51

Yeah, I'm not doing it for storage savings, I'm doing it for predictability: not having to worry about dateformats and timezones which is something I consider part of the UI layer and not anything I want anywhere near my backend data store. I consider it really silly to be converting my timestamp into a string just so MySQL can convert it back into a timestamp for storage (only with a mucked-up local timezone).

bobince 2010-01-30 09:15:12

The SQL date functions could be useful, but in practice I very very rarely need to do queries like WHERE HOUR(t)=12 which can't be done just as easily with ints, and in practice since these functions defeat indexing, there's no gain from using them directly against the column.

bobince 2010-01-30 09:17:14

Answer 3

+2 A:

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.

bobince 2010-01-30 00:08:25

no no... you're right, I don't care about millisecond accuracy at all, I can even pass in the number already reduced by 3 orders of magnitude... I think that will work.

Dr.Dredel 2010-01-30 01:03:00

ansaurus

tags:

views:

answers:

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

related questions