tag:blogger.com,1999:blog-3635447662505002071.post4996208451184469169..comments2022-03-31T03:30:53.887+01:00Comments on Database Science: Can a timestamp be slower than a datetime?dbsciencehttp://www.blogger.com/profile/05472838753270367869noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-3635447662505002071.post-654354507472737812008-11-01T16:55:00.000+00:002008-11-01T16:55:00.000+00:00Hi there,Can you do some news tests?Okay, you've c...Hi there,<BR/><BR/>Can you do some news tests?<BR/><BR/>Okay, you've created timestamp column in table schema, but you use human readable date specification in query.<BR/><BR/>What about if you use timestamp in the queries?<BR/><BR/>SELECT name WHERE registered BETWEEN 1124448300 AND 1225557200<BR/><BR/>Thanks.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3635447662505002071.post-52626682350290672122008-09-02T14:57:00.000+01:002008-09-02T14:57:00.000+01:00Same results here with InnoDB range queries on a n...Same results here with InnoDB range queries on a non-indexed timestamp column, difference of performance is 260% faster with datetime.Unknownhttps://www.blogger.com/profile/14357784876300417395noreply@blogger.comtag:blogger.com,1999:blog-3635447662505002071.post-47931694065512798622008-08-31T22:40:00.000+01:002008-08-31T22:40:00.000+01:00I found the same results two weeks ago on MySQL 5....I found the same results two weeks ago on MySQL 5.0.32 using plani Myisam tables.<BR/><BR/>I was on the way to turn some datetime's into timestamps, but this effect allowed me to scratch that TODO item off the list without any work :)<BR/><BR/>Still find it strange though, and curious to hear about the reason!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3635447662505002071.post-18135546231091003802008-08-30T21:49:00.000+01:002008-08-30T21:49:00.000+01:00That bug BTW looks very much like a Daylight Savin...That bug BTW looks very much like a Daylight Saving problem I've experienced in migrations before.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3635447662505002071.post-25536505838106195852008-08-30T04:16:00.000+01:002008-08-30T04:16:00.000+01:00After reading your blog I setup a test and found s...After reading your blog I setup a test and found similar results...and elevated processor utilization. I suspect that there is some overhead of converting the timestamps to dates for display. I interpret the manual as saying that timestamp data is stored as an integer number in the database, so thats a conversion that has to be done by the engine. <BR/><BR/>if you change your where clause to use: <BR/><BR/>...purchaseDate >= TIMESTAMP('2001-07-01')+0 .....you may notice a n improvement in performance as I did. <BR/><BR/>I generally advise development teams to avoid the timestamp datatype in MySQL for business data such as a purchase date since the datetime datatype is much more universal for this.John Dzilvelishttps://www.blogger.com/profile/06400950302176530075noreply@blogger.comtag:blogger.com,1999:blog-3635447662505002071.post-79857356434289153372008-08-30T03:24:00.000+01:002008-08-30T03:24:00.000+01:00I was actually wondering about the same thing toda...I was actually wondering about the same thing today.. And wondered if it was a wise idea to go with INTs altogher..Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3635447662505002071.post-81178990608415227402008-08-30T01:55:00.000+01:002008-08-30T01:55:00.000+01:00I bet this case is unique to using a timestamp col...I bet this case is unique to using a timestamp column as the leftmost part of an InnoDB clustered index.<BR/><BR/>To try to isolate this, how about repeating the test by creating otherwise identical tables, but change the TIMESTAMP column to INT and the DATETIME column to BIGINT, populating them by using UNIX_TIMESTAMP() to preserve distribution.<BR/><BR/>And the invalid timestamp error screams of time zone conversion involving daylight saving time, even though the US didn't switch to DST on that date.<BR/><BR/>Since you must be using a US time zone, try rerunning all the tests after setting the time zone in use to UTC.Anonymousnoreply@blogger.com