Why MySQL’s (SQL) DATETIME can and should be avoided

This post was written by eli on March 15, 2009
Posted Under: MySQL,Software

I warmly recommend reading the comments at the bottom of this page, many of which go against my point. While I still stand behind every word I said, in particular for web applications (which I believe is the vast majority of MySQL use), the comments below make some valid points, and single out cases where DATETIME actually is the right thing.

Needless to say, this is a discussion, and we’re all free to make our own mistakes.

SQL DATETIME sucks

MySQL, among other databases, has a column type called DATETIME. Its name seems to mislead people into thinking that it’s suitable for storing time of events. Or suitable for anything.

This is a general SQL thing, by the way, but I’ll demonstrate it on MySQL.

I often find this column type in other people’s database schemas, and I wonder if the designer gave it a thought before using it. It’s true, that in the beginning it looks simple:

mysql> CREATE TABLE stupid_date ( thedate DATETIME, PRIMARY KEY (thedate) );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO stupid_date(thedate) VALUES ( NOW() );
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM stupid_date;
+---------------------+
| thedate             |
+---------------------+
| 2009-03-15 14:01:43 |
+---------------------+
1 row in set (0.00 sec)

That was way too cute, wasn’t it? We also have the NOW() function, which fits in exactly, and puts the current time! Yay! Yay! And if the timestamp looks old-fashioned to you, I suppose there is a reason for that.

But wait, there are two major problems. The first one is that the time is given in the host’s local time. That was fair enough before the internet was invented. But today a web server can be a continent away. DATETIME will show you the local time of the server, not yours. There are SQL functions to convert timezones, of course. Are you sure that you want to deal with them? What happens when you want to move your database to a server in another timezone? What about daylight saving time? Local time is one big YUCK.

(Update: As commented below, the real stupidity is to use NOW(), and not UTC_TIMESTAMP(). The latter gives the UTC time, as its name implies)

Problem number two: Most applications don’t care what the absolute time is. The current time is commonly used to calculate how much time has elapsed since a certain event. To filter elements according to if they were X seconds before now. Is the user still logged in? Has 24 hours elapsed since the last warning email was sent? And so on.

“Solution”: The SQL language supplies a variety of COBOL-like functions to calculate whatever we can ask for. And also an opportunity to get things horribly wrong, because the SQL statement became way too complicated.

Use POSIX time() instead

Sorry, didn’t mean to scare you off. It’s really simple: Any modern operating system, even Windows, will readily supply you with the number of seconds since January 1, 1970, midnight, UTC (that is, more or less GMT). This is also called “seconds since the Epoch” or “UNIX time”.

No matter where the computer is, what timezone it uses or what programming language you’re using, this simple integer representation will show the same number at any given moment.

You can, in fact, obtain this number from MySQL directly:

mysql> SELECT UNIX_TIMESTAMP(thedate) FROM stupid_date;
+-------------------------+
| UNIX_TIMESTAMP(thedate) |
+-------------------------+
|              1237118503 |
+-------------------------+
1 row in set (0.00 sec)

This means, that 1237118503 seconds elapsed since the Epoch (which is a global time point) until 14:01:43 in Israeli LOCAL time of the day I wrote this post. So now we have an integer number to work with, which is handy for calculations, but things will still get messy if we try to move the database to another server.

Store the number instead

If we are interested in working with integers, why not store the integer itself in the database? We could go:

mysql> CREATE TABLE smart_date ( thedate INTEGER UNSIGNED, PRIMARY KEY (thedate) );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO smart_date(thedate) VALUES (1237118503);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM smart_date;
+------------+
| thedate    |
+------------+
| 1237118503 |
+------------+
1 row in set (0.00 sec)

That wasn’t very impressive, was it? The first question would be “OK, how do I get this magic number, now that I don’t have the NOW() function?”

The short and not-so-clever answer is that you could always use MySQL’s UNIX_TIMESTAMP( NOW() ) for this. The better answer is that no matter which scripting or programming language you’re using, this number is very easy to obtain. I’ll show examples below.

As for the magnitude of this number, yes, it’s pretty big. But it will fit a signed 32-bit integer until year 2038. I presume that nobody will use 32-bit integers by then.

And finally, one could argue that DATETIME is convenient when reading from the database directly. True. But for that specific issue we have the FROM_UNIXTIME() function:

mysql> SELECT FROM_UNIXTIME(thedate) FROM smart_date;
+------------------------+
| FROM_UNIXTIME(thedate) |
+------------------------+
| 2009-03-15 14:01:43    |
+------------------------+
1 row in set (0.00 sec)

And again, this is given in the computer’s local time. Which is fine, because it’s intended to be read by humans. In particular, humans who easily translate time differences between their server and themselves.

Obtaining Epoch time

Just to prove that it’s easy to know what the “Epoch time” is in any language, here are a few examples. Wherever it’s really simple, I’m showing how to convert this format to human-readable format.

In Perl:

print time();
print scalar localtime time(); # Local time for humans

In PHP:

<?php
echo time();
echo date('r', time() ); // Local time for humans
?>

In Python:

from time import time;
print time();

(note that the time is returned as a float number with higher precision)

In C:

#include <time.h>
#include <stdio.h>

int main () {
  int now = time(NULL);

  printf("%d seconds since the Epoch\n", now);
  return 0;
}

In JavaScript:

<script language="JavaScript" type="text/javascript">
now = new Date();
alert( now.getTime() / 1000 );
</script>

In this case, the time is shown with a fractional resolution.

The JavaScript example is not really useful for a database application, because the time is measured at the computer showing the page. In a website application, this is just anybody’s computer clock, which may be wrong. But it’s yet another example of how this time representation is widely available.

Conclusion

Drop those DATETIME columns from your tables, and use a simple, robust and handy format to represent time. Don’t let the database play around with a sensitive issue like time, and don’t risk getting confused by different functions when calculating time differences. Just because the DATETIME column type exists, it doesn’t mean there is a reason to use it.

Enjoy the database on what it’s best at: Storing and collecting information.

Reader Comments

Hi,

I read this article and although I find you have some valid remarks there I cannot shake off the impression that you are overlooking some aspects.

- NOW() vs. UTC_TIMESTAMP()

In case you really want to be independant of the timezone I suggest NOT using NOW() as that is localtime. UTC_TIMESTAMP() is what you need to use. It makes no sense saying that the webserver is half a globe away and still use NOW in those examples. Maybe you know this, maybe not but it might be worth mentioning as you focus on timezones.

- Mysql Partitioning schema’s. TIMESTAMP just doesn’t work (as in mysql 5.1) for partitioned tables at the moment, the engine isn’t optimized voor BETWEEN type queries. When using DATETIME and TO_DAYS function you can partition on that collumn. So dropping DATETIME from schema’s isn’t always feasable nor desirable.

But thanks for sharing the different insight there..

Glenn

#1 
Written By Glenn Plas on May 26th, 2009 @ 16:30

Thanks for that remark. I added a small remark about UTC_TIMESTAMP() in the post, which indeed solves the time zone issue.

As for partitioning, that’s beyond me (for now, at least).

#2 
Written By eli on May 26th, 2009 @ 20:56

Sorry, I don’t quite follow. I thought DATETIME, TIMESTAMP, and INT only differ in the way they store data; what you put into them depends on your usage and not the table structure syntax.

In other words, couldn’t I create/use a DATETIME field and on every new record insert UTC_TIMESTAMP() instead of NOW()?
If that is the case, the real issue is storing universal time vs local time, isn’t it?

Thanks.

#3 
Written By Michael B on May 28th, 2009 @ 19:54

Yes, you can create a DATETIME to store UTC time. To be honest, I wasn’t aware of UTC_TIMESTAMP() when I wrote the post.

The bottom line is that nothing stops you from using DATETIME to store UTC time, and nothing stops you from displaying Epoch time as something meaningful within MySQL.

My main point still remains: There is nothing to DATETIME which gives it an advantage over just Epoch time, but somehow I feel safer when time is stored as a plain integer (and I’m sure MySQL won’t think about fiddling with it).

Except for the last remark about partitioning, which I can’t comment much about.

#4 
Written By eli on May 28th, 2009 @ 20:10

Hi, I’m back. I can see why some people would prefer to store dates in INT formats (Unix Timestamps) because it could simplify some calculations and data conversions from one medium to another.
But I looked at the MySQL site and realized 2 important limitations (right now) with using INT or TIMESTAMP that doesn’t occur with DATETIME. A valid range for UNIX Timestamp (right now) is 1970 through ’2038-01-09 03:14:07′. That is because it is stored as a 32 bit value. So, you might not be able to use it if you were writing some historical type of application, such as dealing with historical birth dates or astrological events (which may need granularity to the second). Also, you couldn’t deal with dates past 2038, until they change the format to 64 bits.
On the other hand, using TIMESTAMPS saves half the amount of space (only 4 bytes as opposed to DATETIME’s 8 bytes) which could speed up calculations and record getting.

So, in summary, DATETIME should be avoided unless you need to store historical or far future dates right now.

Source: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

#5 
Written By Michael B on May 29th, 2009 @ 17:53

The 32 bit limitation is indeed an issue. Still, year 2038 occurs when the epoch time reaches 2^31-1, which is the maximum for a *signed* 32-bit integer.

And given the invasion of 64-bit computers, I would expect the UNIX standard to shift pretty soon.

Until then, if your database deals with pension funds, stick to DATETIME. ;)

#6 
Written By eli on May 29th, 2009 @ 18:02

Hi Eli, I think your post is has a misleading subject. As you have already noticed, DATETIME can and should be used, depending on the situation. Also, the issue you report regarding time zone effect you will also run through it when you use TIMESTAMP, as it’s also timezone affected (thus meaning that the Epoch (UTC) offset will still be affected by time zone conversion).

There are also performance considerations to bear in mind when choosing the datatype:

Check this out:

http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/

This was a test I made to study performance on a “from human readable to database” and the opposite. As you’ll see, DATETIME beats TIMESTAMP like ‘always’ for large datasets.

Hope that helps, and maybe you could fix the subject :)

#7 
Written By Nuno Tavares on September 7th, 2009 @ 13:20

Hello.

Thanks for your comment. The title isn’t misleading, it reflects my opinion. ;)

I suggested to obtain the time in the script, and pass it to the database as an integer. The benchmark you pointed at is interesting, but it shows that INT is slower when the database itself calculates the Epoch time for each line it works on. It also shows that INT is faster when the epoch time is precalculated, which is the realistic case.

#8 
Written By eli on September 7th, 2009 @ 13:42

אחי אחלה פוסט!

#9 
Written By gal on November 3rd, 2009 @ 13:26

Hey, nice post, just looking into this myself.
I was wondering the same things about the timestamp format.
Reading this I’m definately going the signed int way!
Storing in the database as unixtime and taking out
and manipulating with perl, sounds like the way :)

thanks again

David

#10 
Written By David on January 8th, 2010 @ 16:09

I never tought abaut this aspect of precision before. Thanks a lot for your assististment, to find the right decision for my future one and only date format in databases.
Gino

#11 
Written By Josef G, Knust on January 21st, 2010 @ 04:31

Calling UNIX_TIMESTAMP() without any parameters returns the current time in UNIX time. It’s that easy!

#12 
Written By Tim E on February 9th, 2010 @ 23:40

Yes, I agree for keeping record of a table which acts as a log. However, some DBs won’t allow you to put a hard date into a timestamp. So if you’re doing something like keeping track of the date something is due and the date something ships and the due date is entered by an order processor then you’ve got to use the DATE type.

My point really is that there are no hard and fast rules just personal opinions when it comes to writing software or designing databases.

#13 
Written By Vinnie on February 13th, 2010 @ 18:01

Well, my point *was* that there is one simple fit-all rule, which is: use INTEGER UNSIGNED, period. If the date is not “now”, let the script convert the relevant date to an integer, and again, store it as such.

#14 
Written By eli on February 13th, 2010 @ 18:46

I’m going to have to agree with ElI and say you should not use DATETIME.

Sure you can /try/ to store a UTC date in a DATETIME column, but it’s only PRETEND because mysql is really treating it as local. So if you try to get your time back as an integer in UNIX time by calling UNIX_TIMESTAMP(mydate) it treats the stored date as local and does a local->UTC conversion that screws it up.

#15 
Written By sshock on February 15th, 2010 @ 03:58

First of all – Great post, Thanks!
One question remains unclear to me regarding the DST-
assuming I’m using TIMESTAMP on event start date in my website, please follow the scenario:
1. On the server it’s now 23:00 and someone is adding an event for tomorrow at 13:00.
2. at 2:00 on server time, due to DST, the time is going back an hour to 1:00.
3. our ‘someone’ is going to his calendar and checking his event.
-What will he see? will it be 12:00, 13:00 or 14:00?
Thanks

#16 
Written By Guy on March 8th, 2010 @ 19:16

Hi, I’ve written extensively about mysql datetime and timestamp columns on my blog and long story short, while I think you raise some interesting points, I couldn’t disagree with you more. At no point did you acknowledge the huge advantage the intrinsic types have in terms of basic readability or date range queries.

Comparing a unix timestamp to a datetime is very much an apples to oranges comparison — an integer is 32 bits, whereas a datetime is an 8 byte column. With the extra storage, you can represent date values far beyond the range of the unix timestamp.

While not exactly the same, the mysql timestamp is essentially equivalent to an integer storing the unix timestamp, but with the benefits of allowing for the use of mysql date functions.

#17 
Written By David on March 10th, 2010 @ 06:55

Thanks for the article! The pitfalls of DATETIME columns was something that got by me somehow. In my case to avoid massive code changes we’re converting to TIMESTAMP columns. Either way you go, POSIX integers or TIMESTAMP columns the potential evil of using DATETIME columns is something nobody should ignore. For anyone who plans on converting to TIMESTAMP, make SURE you read the manual carefully about setting defaults, especially when you want either nullable columns or if you don’t want the TIMESTAMP to change when the record is updated.

One thing I didn’t see mentioned here is the fact that a DATETIME stored based on the servers current time zone, unless I’m missing something, CANNOT be reliably converted to UTC. At the point that clocks get turned back you end up with a number of local times that can literally represent more than one UTC time. If I’m wrong, someone correct me, but I can’t imagine that this is possible…ugly indeed.

#18 
Written By Tom on July 20th, 2010 @ 17:42

Hi everyone!
I’ve recognized that some of you are worried about the 32bit-thing. What if you simply take away the website’s int start time (because we don’t want negative integers, do we), from every current time you store. Then you have a much smaller int to work with, and still you can store the start time somewhere in your scripts or in mysql.
Start time: the date when the server begin to run in this aspect.

Wish it’s understandable, it’s late at night ;)

#19 
Written By mcchicken on August 24th, 2010 @ 01:15

I still don’t see the point in this post. I use epoch a lot, but there is nothing here to convince me that I should always use it instead of datetime.

#20 
Written By Andrew McGregor on August 25th, 2010 @ 12:23
#21 
Written By Ivy on January 11th, 2011 @ 19:50

I had struggled with DATETIME for a while until I switched to TIMESTAMP with default value as CURRENT_TIMESTAMP.

Appreciate your article :)

#22 
Written By Eric Nguyen on February 10th, 2011 @ 11:38

MySQL is still very young on date/time functionalities and thus isnt as optimized with datetime as some other engines. While I am not a MSSQL fan, I have used it extensively in one of my previous job, and its support for date/time have proven to be invaluable.

Date time is a part of many databases. To avoid it is to limit yourself.

Previously, MySQL didnt have datetime capability and thus UNSIGNED INT were used in its stead. While the range of an UNSIGNED INT will reach its limit soon, this is NOT a MySQL problem. You can always use bigint to get around this. The real problem with capacity lies in PHP.

On the issue of timezone, this has always been the core of the problem for developers for ages. The real solution to this is to store the value in a universal and consistent format, eg using GMT and convert it to which ever timezone needed before displaying. This should be a discipline for the developer, rather than the actual burden of the database. All the necessary functionalities for this conversion are available to us. Learn to use it.

#23 
Written By P.H on February 15th, 2011 @ 03:17

I’m sorry, but this is terrible advice. I know, because I used to do it myself!

First, the fatal flaw that’s been mentioned already: How do I represent someone’s birthday using a TIMESTAMP? If I have to store a birthday from 1947, TIMESTAMPS and INTs simply aren’t going to work in any reasonable way.

Second: MySQL has a hard time using indexes when functions are involved! Searching using a WHERE clause of “WHERE myDate < UNIX_TIMESTAMP(‘whatever’)” is NOT the same as running “WHERE myDate < ‘whatever’”. The query optimizer functions much better given constant values. If you start using complex date functions and conversion math in your queries, you might get some that are not able to use your indexes and have much slower queries as a result. You can avoid this by writing conversion code in your app to do the conversion beforehand before the query is sent to MySQL, but now you’re just making things more complex and creating work.

I understand what you’re saying about MySQL doing a bunch of time conversions based on the server settings and the timezone setting of the client connection. It can be a problem. This is something that developers have to be aware of, however it is NOT justification to say “Never use DATETIME”.

If you are dealing with timezone sensitive data or data that needs conversion between timezones, there is just one thing you have to do: always store your values in GMT. That’s it. 100% of your database date/time problems are solved. And guess what? It’s easiest to do this by storing them with a DATETIME column.

#24 
Written By Blake Soley on February 28th, 2011 @ 23:17

I’m starting to realize, that I wrote this post from a somewhat egocentric point of view, as someone whose interest in time is only for events happening while the computer was running. I can understand why people who use need to define birth dates or any other historic events don’t approve with my view.

But after all, MySQL is considered primarily a web database, and most uses of time in such are literally timestamps.

And I still find it hard to believe that anything is easier for a database than a “WHERE timestamp

And yes, if someone is silly enough to store the time in that COBOL-like format in his or her application, and then go WHERE myDate < UNIX_TIMESTAMP(’whatever’), well, well, what can I say…

#25 
Written By eli on March 2nd, 2011 @ 23:56

I like your idea as it provides efficient and accurate way to store timestamps for auditing (at least you know and control the precision). OTOH, I agree that to store a date on which you do operations it may not be appropriate.

For the record, I’ve seen a financial application that stores the dates in an integer indicating a number of days relative to an epoch date (1901). The good thing is that this provides a very efficient retrieval of the date. The bad thing is that this makes exploring the database harder.

#26 
Written By Stephane Carrez on March 8th, 2011 @ 00:35

CREATE TABLE stupid_date

Thanks for this article, I needed a laugh while shuffling through a date-time issue in Zoho Creator

#27 
Written By SnowJoe on May 3rd, 2011 @ 22:53

Thanks, this was precisely what I was looking for and has been a very handy debate to find. For me it gets down to whether datetime or timestamp is better for UTC time storage where server and local timezones differ and it is necessary for conversion on display/retrieval.

#28 
Written By Akram j Hindi on May 13th, 2011 @ 14:22

I don’t mean to be stupid, but if you were moving servers across timezones what’s stopping you changing the server’s timezone? If I had a server in the Americas I wouldn’t really care about the American timezone; it’d make developing on more confusing.

#29 
Written By Peter on May 17th, 2011 @ 17:27

Excellent !!!!

#30 
Written By Pradeep on June 2nd, 2011 @ 21:53

Guy’s comment shows the pitfalls of following this article’s advice — storing time in UTC is just plain wrong unless you store the local DST offset with it. At that point, you might as well use a local DATETIME with an additional column to specify the local timezone. Now you can move servers across continents, using MySQL’s helpful CONVERT_TZ() function to convert these values to server-local time for processing. You’ll never have to update your records after such a move, either.

In short, DATETIME can and should be used when you care about the issues this article discusses. It’s just not sufficient on its own to record an instant in time, and neither is TIMESTAMP for that matter.

#31 
Written By Tad on June 22nd, 2011 @ 21:07

In a fairly set up computer, the time translation from and to local time is done by the operating system using built-in functions. That includes taking Daylight Saving Time into consideration. So all in all, UTC represents what it should: An objective instance in time.

If the database moves, nothing changes. If the user moves and updates the computer regarding the changed timezone, the translation back to localtime will change, as it should.

Consider, for example, someone who sets a reminder to call his aunt and wish her happy birthday just before she goes to lunch. If this person travels abroad, that reminder might wake this person in the middle of the night. That’s correct behavior.

The only problem arises when the Daylight policy changes between setting a future date and reaching it. If this timestamp was a meeting between people in a local area, a mismatch may occur if some participants marked the time in local format (such as writing it on paper). On the other hand, if this was a teleconference meeting among people from different timezones, the back-and-forth translation will be a savior.

#32 
Written By eli on June 23rd, 2011 @ 10:25

Sharp, brilliant, simple explanations. Thanks

#33 
Written By Leonardo J. Portillo V. on August 25th, 2011 @ 23:20

Storing all your date-time data using integers is just silly … as mentioned in a few readers’ posts …
a). Many date-time functions will be sorely missed if you use integers.
b). Integers will NOT solve your time zone problems.

Use GMT dates when dealing with time-zone sensitive data for global conformity.
AND use built in date/time data types so as to maximise database functionality.

Consider a situation where the web-based software is hosted in New York, with a multitude of users all over the world creating invoices, receipts and credit notes.

John is in Cape Town and creates a Sales Invoice at 10:00am Cape Town time (0800 GMT).
Mary is in London and creates the corresponding Cash Receipt 20 minutes later at 8:20am London time (0820 GMT).
Bjorn is in Greenland and creates a Credit Note against John’s Invoice 20 minutes later at 5:40am Greenland Time (0840 GMT).

Invoice created 10:00, receipted 08:20 and cancelled 05:40.
If we store users’ local dates, the dates on the documents won’t make any sense, even if you use integers.

If we store the GMT equivalent, the dates will make sense, regardless of where the server is or where the users are or the data types we choose for storage.

Where people get off the auto-aggrandizement band wagon – I have no idea. ;-)

#34 
Written By Johnny Boy on September 21st, 2011 @ 08:04

While I do agree that time-related functions are missed out when using integers (so e.g. looking for invoices issued on the first of each month won’t be that fast), I find the example you showed irrelevant to the discussion.

If all entities save the time in epoch time, the subsequent events will have logical relations in the database, since all computers in the have the same epoch time clock.

If the data is viewed from any place in the world, it’s up to the presenting software to convert the stored epoch time to any timezone that makes sense. So the user in Cape Town will see its local time, and the one in England will see GMT. Or alternatively, if there is good-old office paper involved, all papers are printed with a predefined time zone.

#35 
Written By eli on September 21st, 2011 @ 09:32

I couldn’t get past the third or fourth or was it fifth use of the word “stupid” or “stupidity.”

#36 
Written By Dino on September 25th, 2011 @ 21:34

I have to respond to this because it shows up on a first page google result. Please, please, please don’t make SQL do your heavy lifting. All the issues discussed in this article should have been taken care of with your application code. SQL is not a hack for lazy programming. Datetime should ALWAYS be used instead of timestamps.

Datetime will also allow you to match such things as “get all entries created on the 30th minute of the hour, on the 10th day of the month every leap year for the last fifty years”, etc. You will not get the power, performance and flexibility of a timestamp that you can get out of datetime.

Worse than bad coding, poor database design can lock in software failure and is by far the most expensive and time consuming thing to fix on the next iteration.

#37 
Written By David Thomson on October 24th, 2011 @ 06:35

Hi,
After i read some of the comments, i think that is better use DATETIME, with UTC_TIMESTAMP().
Well, still not sure.

#38 
Written By OnLine on October 30th, 2011 @ 19:00

I’m in the process of designing a database and found this article.

You are 100% right! Thank you for sharing this info, it helped me a lot (less to code xD).

#39 
Written By GeriBoss on November 3rd, 2011 @ 11:57

I also agree use UTC_TIMESTAMP

#40 
Written By Li on November 4th, 2011 @ 04:25

in a case where the default timezones for system, mysql, and php/python/whatev not may not be matching or are misconfigured — what might the chances of returning a miscalculated value would be?

#41 
Written By i like the thinking, so then: on November 5th, 2011 @ 06:23

So very very wrong.

#42 
Written By Petah on November 6th, 2011 @ 02:59

I absolutely agree with the author of the blog post. There is very little reason to use DATETIME. Let’s go over some of the counter arguments:

a) You can’t record a date before 01/01/1970. Sure you can. Just use a negative integer. Most scripting languages will recognize that. Example:

$ perl -E ‘print scalar localtime -11234569999;’
Fri Dec 27 22:13:43 1613

b) DST changes will bite you. No it won’t. Use a UTC epoch time integer and leave it up to the scripting language to convert to localtime. JavaScript’s Date does a wonderful job:

var epoch = localDate(1320901711);
console.log(epoch);
/* it will show the date in YOUR timezone, given that your browser is sane */

c) You can’t run queries looking up the n-th day or the m-th minute. Sure you can. Run your queries using FROM_UNIXTIME. A bit slower, but if you need speed for *that* you may want to design your table differently.

d) The epoch integer is 32 bit and at 2038 it’ll roll over. Rest assured – by 2038 all operating systems will use a 64-bit epoch. Make sure to define your dates as UNSIGNED BIGINT.

I don’t see any reason to do all the DATETIME gymnastics and use any of the time functions that MySQL provides. In 99% of the times you need to quickly add or subtract seconds from your date.

#43 
Written By minimalist on November 10th, 2011 @ 07:17

Thank you for taking a stand. I’ve been forced by company policy at a couple of places to use DATETIME and I loathe it. I’ve preached the virtues of INT timestamps and now that key/value pair data (NoSQL) is getting popular developers are catching on. Figures. ;-)

#44 
Written By Randy on November 10th, 2011 @ 21:38

Nothing to offend, but I think the author voiced against DATETIME mostly due to the lack of knowledge about the same. I would say the default option should be DATETIME. Here is Why:

1. You can store any date.
2. You can read the date in actual date format without any extra functions (helpful if you have lots of queries that deal with time)
3. You can use Mysql’s datetime related functions (such as MONTHNAME() if you want to know the month name) which is not at all possible if you use epoch int format. I noticed that someone above pointed epoch is better because “In 99% of the times you need to quickly add or subtract seconds from your date.” Well, you can do this quite easily with DATETIME, just use DATE_ADD() function.
4. Lastly and more importantly, DATETIME is meant to store date/time, while int is meant to store int. When you have a data type to store only date related data, why would you want to use a format that is not really designed to use it? Besides, there is always scope for getting more features for DATETIME as part of expansion.

Thanks for reading!

#45 
Written By Akhthar on November 15th, 2011 @ 17:55

Just for the record, the author of this post was highly aware of the points made above, and still. As for point #4, it’s exactly what the author is opposing. In particular, as the time *is* an int in so many applications.

#46 
Written By eli on November 15th, 2011 @ 18:02

I would agree with an above comment recommending that the title be changed. While everyone has the right to an opinion (and I appreciate you voicing yours), I gather your position has changed somewhat and think it would be courteous to visitors to reflect your revised position.

No need to repeat what’s already been said, so let me point out that MySQL DOES offer a solution for the web apps you cite that simply need to log a current timestamp and it’s a column type of TIMESTAMP. If you use this column type, it’s more likely that your data will make the transition to a 64-bit equivalent than if you are storing as a fixed size INT.

It seems that you’re holding out on the notion of a timestamp being used for applications needing to reflect current time, but it’s important to learn from history. In the 80s, no one figured apps would be running 20 years later, yet that was exactly the case in 2000. 2038 is less than 30 years later and it’s bold to assume that no code written today will be in use 29 years later (particularly if that code incorporates business logic).

There are certainly cases where human readable datatypes aren’t the best (IP addresses being a prime example), but I’m confident dates & times aren’t one of them. Thanks for voicing your opinion.

#47 
Written By Matthew on November 28th, 2011 @ 09:44

So how would you prune an entry that was created 7 days ago? I was thinking about creating a row called “mydate” or somethin with this on it: UNIX_TIMESTAMP( NOW() ). Then, the delete query would be like this:

$dat3 = time() – 172800; //now-(2days)
if (!mysql_query(“DELETE FROM tiempo WHERE thedate<'$dat3'")) die ("Not deleted.");

is there any other method? something more 'sofisticated'? thanks.

#48 
Written By xwalero on December 2nd, 2011 @ 01:59

i have an application for which datetime suits the requirements perfectly, exactly, and any other field type or technique is inconvenient and requires far more code.

it’s for using datetime as a search field.

datetime fields, when you use LIKE, are treated exactly like varchar, char and text fields.

this is incredibly useful.

i DO NOT want the search to be carried out by year, month or date – i SPECIFICALLY want the users to be able to literally type in ANY text – whether it be 2, 20, 200 or 2008, and for any dates matching that search text AS DISPLAYED e.g. 2008-02-20 – to come up in the search.

as this is a curses-based (text console) application, there *is* no “internet” involved: there is no interest in, nor any requirement for, UTC time, POSIX time or any other kind of time *other* than “NOW()” and datetime fields.

the bottom line is, eli, that you’ve made a number of assumptions – the primary one of which is that everyone in the world uses SQL servers for “web servers”. they don’t.

#49 
Written By Luke Leighton on December 8th, 2011 @ 17:11

Under the covers, MySQL stores and manipulates DATETIME as time_t, the same thing as POSIX time.

Your recommendation doesn’t really provide any benefit from the point of view of applications using the database, limits the ability of troubleshooters to have the date transformed for them, and hamstrings anything that might want to operate within the database based on the local time.

To use DATETIME properly, always store things relative to a single “local time”. If you can’t pick one, set all your systems to UTC. Always translate to/from the user’s timezone for user interfaces.

#50 
Written By Alan on January 24th, 2012 @ 01:04

This is a very interesting discussion from the point of view that I have been dealing with this exact problem for a few days. In the end, we have a very elegant simple solution. Let me describe my problem… I am writing an application that processes metadata from all over the world. There are distributed servers reading replicated dbs in different time zones. We noticed the “time zone problem” right away.

Consider storing the time “July 4, 12:00″. This could imply the time a store opens on the 4th of July (noon). I stored this in CST. When I read this in a PST zone, the time came back as “July 4, 12:00 PST”. Depending on how you interpret time, this could be considered correct, or, incorrect. I was using a PostgreSQL “timestamp” (note the definition of a PostgreSQL timestamp and a mySQL timestamp are not the same). If I wanted to know what time the store opens in PST, then 12:00 would be the correct value. Let’s look at another interpretation. Assuming I “raised my flag at 12:00 CST”, then in PST, getting 12:00 would be considered incorrect – the flag was actually raised at 10:00 PST.

The solution we are using is to write a custom “type handler” that maps between java.util.Date and the database (we are using myBatis). The idea is that any time a java.util.Date is written, the type handler converts it to a UTC time before storage in the db. Any time a time is read, it is converted from UTC to the java.util.Date. This solution is completely transparent to the application, we just read/write Date. It also works on different dbs (we used mySQL and PostgreSQL). In mySQL, I used DATETIME, in PostgreSQL, I used TIMESTAMP. Queries, comparisons, etc. all work without requiring any reference to global settings or special functions.

Here is the (mybatis) code.

*** Config.xml ***

*** DateMapper.java ***

package main.database.mappers;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.TimeZone;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

public class DateMapper implements TypeHandler {

@Override
public Object getResult(ResultSet arg0, String arg1) throws SQLException {
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone(“UTC”));
Timestamp t = (Timestamp) arg0.getTimestamp(arg1, calendar);
return new java.util.Date(t.getTime());
}

@Override
public Object getResult(CallableStatement arg0, int arg1) throws SQLException {
Calendar calendar = Calendar.getInstance(TimeZone.getTimeZone(“UTC”));
Timestamp t = (Timestamp) arg0.getTimestamp(arg1, calendar);
return new java.util.Date(t.getTime());
}

@Override
public void setParameter(
PreparedStatement arg0,
int arg1,
Object arg2,
JdbcType arg3) throws SQLException {

java.util.Date utilDate = (java.util.Date) arg2;
arg0.setTimestamp(
arg1,
new Timestamp(utilDate.getTime()),
Calendar.getInstance(TimeZone.getTimeZone(“UTC”)));
}

}

#51 
Written By Brett Lindsley on February 16th, 2012 @ 19:16

Hi!

Nice blog!

I am having a problem.

I am storing dates as TIMESTAMP i.e. 1329833584. Now I want to get data for today’s date (or any specific day). What would be the query?

Thanx in advance!

Regards!

#52 
Written By Mohal on February 22nd, 2012 @ 12:15

Thanks for postting this, i’ve read a lot of mixed information on how to store time in mysql, but I think after looking over this and the discussion i’ll go with UNIX_TIMESTAMP.

#53 
Written By Josh on February 24th, 2012 @ 12:21

What I want is to be able to store regional datetimes and have them human readable without the need to convert them and have then calculable.

2012-02-27 13:05 Europe/London

But the issue with this is that the datetime library changes fairly frequently because of things outside of our control (geopolitical, energy conservation measures, local government/state, etc.). So today, the offset from UTC may be +500, but tomorrow, it could be different. Add to that DST changes which aren’t consistent world wide. And without the datetime library being uptodate, the code or SQL server won’t know when the datetime was in the past.

This means UTC for everything. No matter what.

#54 
Written By Richard Quadling on February 27th, 2012 @ 15:10

Thanks you simplified a difficult concept for me to wrap my head around!

#55 
Written By Erick on March 16th, 2012 @ 22:23

yes utc

#56 
Written By Anonymous on October 22nd, 2012 @ 02:34

Creating a primary key on DATETIME isn’t a good idea if you’re expecting multiple inserts/second or even updating the DATETIME

#57 
Written By ironlung on November 15th, 2012 @ 01:32

All these complications and thought-out ideas are boring. I think I’m going to just going to use DATETIME and see what happens. If things go wrong, I’ll just blame the problems on Tibor, the guy who doesn’t speak English.

#58 
Written By Bilbo Baggins on November 15th, 2012 @ 19:18

As a web programmer who use calendar as main feature, I’d say that I need both.
I use datetime to mark events and timestamp for counting down.
Some other feature goes beyond timestamp. eg : Calculate 1 january 1 to 31 desember 9999.

#59 
Written By Indra on January 11th, 2013 @ 15:49

Thanks, thanks men. your save my life

#60 
Written By gg on January 15th, 2013 @ 10:10

Ok, so you just named your article wrong! :)

You are not complaining about the datetime column,
but about how to get current time :)
Those are two completely different issues…

Sql column with type datetime is actually a VERY useful thing, when you need to extract e.g. actual data => something like
SELECT * FROM news WHERE date_and_time>=’$todaystime’ ORDER BY date_and_time ASC
($todaystime is sent e.g. from PHP and is your real issue)

#61 
Written By jave on February 17th, 2013 @ 14:59

Hi,

i agree with :
storing time in UTC is just plain wrong unless you store the local DST offset with it. At that point, you might as well use a local DATETIME with an additional column to specify the local timezone. rather more when you enter date and time of birth that you will be using for some calculations in medical matters or for some astrological issues timestamps prior to 1970 will make big issues and force you to use algortihms and additional sql functions instead of native ones.

#62 
Written By Manikam on February 27th, 2013 @ 12:58

but if found this on the web and when i think about it, i find that datetime should be used and timestamps too look at what somebody wrote and it’s bloody true for those who have many long trips across the globe or from east to west in north america.
//
The main difference is that DATETIME is constant while TIMESTAMP is affected by the time_zone setting.

So it only matters when you have — or may in the future have — synchronized clusters across time zones.

In simpler words: If I have a database in Australia, and take a dump of that database to synchronize/populate a database in America, then the TIMESTAMP would update to reflect the real time of the event in the new time zone, while DATETIME would still reflect the time of the event in the au time zone.

A great example of DATETIME being used where TIMESTAMP should have been used is in Facebook, where their servers are never quite sure what time stuff happened across time zones. Once I was having a conversation in which the time said I was replying to messages before the message was actually sent. (This, of course, could also have been caused by bad time zone translation in the messaging software if the times were being posted rather than synchronized.)
//

#63 
Written By Manikam on February 27th, 2013 @ 13:29

Instead of translating between server (local) timezone and UTC/GMT when retrieving/sending data to/from MySQL, just set the connection timezone to UTC, then you can use date functions without any inconvenience… Why with all these comments has no one pointed that out?!?

As for timezone conversion, I find that php’s DateTime class does pretty well and takes the load off of MySQL, although I did write a wrapper class and function to make it not be a pain to use.

query(“SET time_zone=’+0:00′;”);

#This helps when using php’s date functions
date_default_timezone_set(“UTC”);
?>

#64 
Written By Chinoto Vokro on March 28th, 2013 @ 00:29

Oh great your comment system ate the first three lines of my php.
MySQL needs timezone tables in order to use timezone locations (which you need if you want to handle DST within MySQL).
http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

#65 
Written By Chinoto Vokro on March 28th, 2013 @ 00:35

Well, using INT for datetime seems so silly to me that I won’t argue about this, but a few people pointed out that using FROM_UNIXTIME(myIntField) would be almost the same as using a native datetime field. It seems these people never worked on big projects. I work on tables containing millions of records. Using FROM_UNIXTIME will be worse than “slower”, it will be totally unusable. By using INT date fields you are bypassing indexes when using date/time functions. I guess it’s OK if you’re working on small projects, although only for clarity I’d still prefer a real datetime field.

#66 
Written By Willy on April 4th, 2013 @ 19:16

excerpt from High Performance MySQL, 3rd Edition:

“Special behavior aside, in general if you can use TIMESTAMP you should, because it is more space-efficient than DATETIME. Sometimes people store Unix timestamps as integer values, but this usually doesn’t gain you anything. The integer format is often less convenient to deal with, so we do not recommend doing this.”

#67 
Written By Pseudonym Joe on May 31st, 2013 @ 20:52

It will trouble if you use INT when in large table.

#68 
Written By Tas on July 19th, 2013 @ 20:14

This is so wrong in many ways.
For whoever is reading this article, who probably got here seeking for help, let me clarify this:

DATETIME is perfectly fine and is a great choice if you are using the db for your own site/app.

Facts:
-DATETIME stores dates in the format yyyy-mm-dd HH:mm:ss
-It behaves pretty much like a text field, as it will NOT change when you transfer the data from one server to another, contrary to TIMESTAMP.
-The first example shown at this page occurs because of NOW() which returns the actual date obviously from… the server which is run at.
-Unix time would be fine if you are going to return the stored date against external requests from users at different time zones, like in a web service (and even then DATETIME can still work) or if you need more precise time.
-Working with DATETIME seems to be easier than working with Unix Time and is human readable.
-I do not know why I am still here.

If you need to convert time zones in you DATETIME field simply use CONVERT_TZ() ie: CONVERT_TZ(datetime_field, ‘+00:00′, ‘+01:00′) will adjust +1 hour timezone diff.

#69 
Written By DEMon on February 14th, 2014 @ 12:45

Mysql’s bigint also great to store a full datetime:

2014-04-10 12:34:56
store as unsigned bigint number:
20140410123456

… WHERE value < 20140410123456 AND …

Cheers! :)

#70 
Written By Mr.J. on April 11th, 2014 @ 02:21

Thank you

#71 
Written By Guru on July 5th, 2014 @ 06:08

MySQL has a very convenient way to deal with multiple timezones. In my case, I have a web based app that can have multiple users from different timezones. They need to get some stats in their local time. With MySQL it is a lot easier than other free databases that I evaluated. First of all, we store all data in UTC. Then when the app makes a query, it simply prefixes the SQL queries with the “set timezone ” command. For example:

set timezone ‘US/Eastern’;

After that, we can run our queries as if the local time was US/Eastern without worrying about how to deal with timezone offsets and DST. It has avoid a lot of complexity. We can use group by day, week or month, and all the groupings are per the timezone and not grouped by UTC days or weeks etc. So in my case, MySQL’s datetime field is very convenient. We tried moving to other DBs due to other limitations in MySQL but all the other DBs say “store data in UTC and handle timezone stuff in your app”. This is really not trivial when you need to run “group by day” at the app layer.

You need to run a script to have mysql able to do this, more info can be found here: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

#72 
Written By hacker101 on September 11th, 2014 @ 01:26

Trackbacks

Add a Comment

required, use real name
required, will not be published
optional, your blog address