Why MySQL’s (SQL) DATETIME can and should be avoided
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
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).
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.
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.
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
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. ;)
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 :)
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.
אחי אחלה פוסט!
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
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
Calling UNIX_TIMESTAMP() without any parameters returns the current time in UNIX time. It’s that easy!
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.
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.
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.
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
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.
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.
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 ;)
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.
Very informative post, thank you Eli.
Check this out:
http://stc0.wordpress.com/2011/01/10/a-way-of-inserting-new-rows-using-utc_timestamp-in-a-double-or-bigint-as-primary-key/
I had struggled with DATETIME for a while until I switched to TIMESTAMP with default value as CURRENT_TIMESTAMP.
Appreciate your article :)
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.
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.
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…
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.
CREATE TABLE stupid_date
Thanks for this article, I needed a laugh while shuffling through a date-time issue in Zoho Creator
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.
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.
Excellent !!!!
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.
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.
Sharp, brilliant, simple explanations. Thanks
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. ;-)
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.
I couldn’t get past the third or fourth or was it fifth use of the word “stupid” or “stupidity.”
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.
Hi,
After i read some of the comments, i think that is better use DATETIME, with UTC_TIMESTAMP().
Well, still not sure.
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).
I also agree use UTC_TIMESTAMP
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?
So very very wrong.
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.
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. ;-)
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!
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.
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.
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.
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.
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.
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”)));
}
}
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!
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.
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.
Thanks you simplified a difficult concept for me to wrap my head around!
yes utc
Creating a primary key on DATETIME isn’t a good idea if you’re expecting multiple inserts/second or even updating the DATETIME
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.
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.
Thanks, thanks men. your save my life
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)
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.
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.)
//
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”);
?>
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
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.
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.”
It will trouble if you use INT when in large table.
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.
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! :)
Thank you
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
I’m a bit disappointed that this page is ranked so high in Google – it’s really very bad advice.
Don’t mess around using ints to store dates or datetimes – human readability is incredibly important in any non-trivial database.
IMHO: Use datetimes any time you want to store a date + time (trust MySQL), do timezone handling in the application layer, and try to avoid ever manually writing a timestamp to a table (only use it for automatically stamping a current time on a row).
Youre just a hater, datetime column is awesome!
@aleksandar
That’s an opinion from you. Unix timestamp can be converted from integers to the typical date time format or even specified to something like “Oct 27, 2014″.
Also, why does it matter which format you are using when storing dates?
It doesn’t really matter if the author uses Unix timestamp or if he uses date time. Some people will argue because Unix timestamp only tracks from 1970 and up, but here’s the real question for sceptics.
Why does it even matter if someone uses Unix timestamp? It’s not like this website was made from 1871 and you have to validate the date by using a format that can read the year 1871. I mean 1970 is a pretty close date to the year 2014 instead of 1871.
I find it better to use Unix timestamp because you have a lot of things you can do with it. You can pull it into Jquery real time and do something like what Facebook does with their time format.
I think it’s been made obvious that their are plenty of uses for DateTime by now. I disagree with many whom have said that you must also store the TZ offset in the DB however. You should get that from the client every time and DO NOT store it in the DB. This will make your applications transparently move across time zones. If you store this in the DB then you’ll need to require that your users keep the offset updated. This is just an annoyance because TZ offset is actually a fairly reliable browser metric (your probably using it to store the value in the first place anyway so just keep doing that each page load instead of making it a one time thing). Doing this will mean that the times displayed on your site will be the same as the system clock on each users system.
I don’t quite get the best solution/practice after all, or is there none??
The best solution/practice would be the one that date/time remains same irrespective of where you or your server is located or where viewers are accessing your app, which is what the writer is trying to proffer solution here. I ended up thoroughly confused.
The author claims timestamps leave less “opportunity for things to go horribly wrong”, but such bliss can just as well be born of ignorance. The web is clogged with bad time math examples offered up by cocksure epoch nerds who seem convinced that “tomorrow” begins exactly 86400 seconds from now.
You really might be an electrical engineer, otherwise, if you were developer, you’d know that in most of the cases DATETIME is a far better option than TIMESTAMP. All you need is store your values in GMT, and all your problems will be solved. Simply as that. I’m sorry but your post is totally ridiculous.
Hi All,
is this possible we provide any filed in UTC_TIMESTAM(‘filed’). filed like long value of date,date format.
Good debate, and since this is top of google I’ll throw in my own observation/comments. I saw two comments which are clearly wrong:
1) Never store time in UTC
2) Always use datetime
In relation to the first point, in many applications UTC is the only time you should use at the data layer. The offset/formatting of dates should be handled at the presentation layer based on user config or geographic location. Keeping everything in UTC makes life a lot easier.
In relation to the second point. It’s not black and white. Clearly datetime gives a lot more power and flexibility with all the available Mysql date/time functions, but when it comes to performance and large datasets, an integer can outperform datetime. We have a table with 28M entries in it recording activities in our application over a 2 year period. Running a simple count(1) over a 9 month period is 4 times faster using integer comparison as follows:
mysql> SELECT SQL_NO_CACHE COUNT(1) FROM activities WHERE created_datetime BETWEEN ’2015-01-01 01:30:00′ AND ’2015-09-01 01:30:00′;
+———-+
| COUNT(1) |
+———-+
| 12390949 |
+———-+
1 row in set (12.69 sec)
mysql> SELECT SQL_NO_CACHE COUNT(1) FROM activities WHERE created_integer BETWEEN 1420075800 AND 1441067400;
+———-+
| COUNT(1) |
+———-+
| 12390949 |
+———-+
1 row in set (3.04 sec)
I have read the article, and half of the comments.
I take Eli’s point of view, and I’ll tell you why.
I’ve been using INT timestamps forever. I use them for current time columns or columns that hold near-time timestamps – logs, session expirations etc.
However, I use BIGINT columns for birthdays, sub-second resolution needs or far-future datetimes. I have a set of my own custom classes to deal with all of this. I usually don’t delegate any work on dates to MySQL (as a side effect to offload this burden away from the DB – usually being the slowest and most loaded component in a web app).
My classes optionally accept and work with either INTs or BIGINTs.
I also use the pure integers in sql queries, BETWEEN, etc. I just convert php app’s human-values to the appropriate integers for the SQL. One way or another, I have to construct values for the SQL – from the html inputs.
My classes have means to work with partial dates/times which solves the problem when working with birthdays or year-month-day only datetimes in calculations – other components (hours, minutes, seconds, or even Day and Month in cases where I want to store only Year-Month in DB) are automatically zeroed.
Of course always storing UTC in DB.
So far, for 10+ years I’ve never encountered a problem with this solution.
One pitfall with DATETIMEs in MS SQL I’ve come across recently when trying to migrate a large MS SQL database to MySQL.
It is when you have the edge datetime value when DST kicks in. You have one hour timespan that is invalid (02:00 to 03:00 in CST->CDT). MS SQL accepts 02:00:00 as valid, while MySQL did not. I had to write scripts to fix every field where I have 02:00:00 in the same day DST kicks in for every possible year in the database fields, translate it to 03:00:00, and all this done in the target MySQL (having the fields initially VARCHAR(30), then fixed, then altered to DATETIME). The app that was using the DB is old and just expects DATETIME, otherwise I would have migrated the columns as INTs and done with it.
So, storing UTC in DATETIME field is not too feasible because MySQL still thinks the DATETIME field as local time as others pointed out, moreover the headaches with DST as I pointed out above, we can end up trying to store an invalid datetime in a column, or worse – the RDBMS could not accept an otherwise valid datetime value because of this.
I am working on a project where we need to store employee’s working hours. First, we started with timestamps, and the for example: we have an employee in New York (GMT-5), Berlin (GMT+1) and Sydney (GMT+10), they all work from 8am to 4pm
Here are the timestamps of their start of work for today:
Berlin: Mon Jan 02 07:00:00 UTC 2017 1483340400000
New York: Mon Jan 02 13:00:00 UTC 2017 1483362000000
Sydney: Sun Jan 01 22:00:00 UTC 2017 1483308000000
I want to query for all employees who worked today (date-wise)
SELECT * FROM `logged_work` WHERE timestamp_start >= ’2017-01-02′
and with timestamps I am getting only 2 results, mysql treats Sydney’s time as yesterday.
DATETIME columns were only option to solve it
You say “The short and not-so-clever answer is that you could always use MySQL’s UNIX_TIMESTAMP( NOW() ) for this.” Please don’t do this! UNIX_TIMESTAMP(NOW()) still uses NOW(), which means the UNIX timestamp you generate may be wrong. See the Note with example at https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_unix-timestamp in the MySQL documentation.
Addendum: Why not simply use UNIX_TIMESTAMP() instead? That prevents the ambiguities of NOW().
Note also that if one insists on storing a DATETIME NOW(), one can additionally store the current timezone for disambiguation: TIME_FORMAT(TIMEDIFF(NOW(), UTC_TIMESTAMP),’%H%i’)
Small correction to my message from yesterday, 15:26: The Note with example at in the MySQL documentation is not the right reference. NOW() would never generate ’2005-03-27 02:00:00′ because the clock jumps from 01:59:59 to 03:00:00. Therefore the duplicate values in this case are not the problem. The problem is that NOW() will twice have the value ’2017-10-29 02:00:00′, in CET/CEST. Compare SELECT UNIX_TIMESTAMP(’2017-10-29 01:59:59′)-UNIX_TIMESTAMP(’2017-10-29 02:00:00′); That is, 2017-10-29 02:00:00-03:59:59 CEST will be incorrectly stored as 2017-10-29 02:00:00-03:59:59 CET.
One further improvement to what I said: If you use LPAD(TIME_FORMAT(TIMEDIFF(NOW(), UTC_TIMESTAMP),’%H:%i’),6,’+') when storing the timezone for disambiguation, you can conveniently use it with CONVERT_TZ().
This is silly stuff from someone with a narrow view of the world. I used to store everything in unix_timestamp() format, until I stopped. The reason is that when I’m using a DB browser like adminer I can’t decode the unix timestamps in my head. If I’m checking a login DB to see when someone logged in last, the unix timestamps are useless to me. Plus sometimes I want to just dump a DB without having to write a program to convert the timestamp. I can grep a date from the command line. There’s a lot of reasons to have a date in a DB that can be read by humans
PHP has strtotime() which converts just about anything to a unix timestamps, so it’s easy to convert or compare different format times.
Always set MySQL (or any server) to use UTC exclusively. Problem solved. Simple.
You should never be storing any time data in a local time zone. Convert it on display if you have to.
Times have changed, and front-end backend, OS has changed.
RULES
1. mysql ALWAYS SET ON UTC
2. Datetime is good -> for obvious reasons once you really work on backend a lot – on update on create default values easy to set.
3. If you don’t know how to set your mysql to UTC, you should learn. Here is a start: https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc
(Use google and search for more resources)
4. Doing querying for date ranges are MUCH easier
5. Reading is much easier when you do direct queries (if you ask why, you haven’t got into real systems, only kiddie play systems)
remember – SET MYSQL TO UTC !
Our company still stores some dates as unix timestamps in MySQL. It was convenient in the beginning, and still is, in some ways. But from a DB perspective, it just sucks doing queries, both in the outut and in the query you have to write. And now, our db talks to another DB that uses SQL dates everywhere. So we are constantly converting back and forth.
I would say everything is wrong about this post. None of the issues that are listed (all real issues) couldn’t be also solved with SQL dates using UTC. But the biggest issue is that MySQL doesn’t support 64-bit unix timestamps, which means if you need to use a future date after 2038, everything just breaks.
https://bugs.mysql.com/bug.php?id=12654
The author’s assurance that everything will be fixed before 2038 is surely true. But guess what? We need to use future dates now, not in 2038.
Excellent! you saved my time,i was looking for that. thanks