And finally, one could argue that DATETIME is convenient when reading from the database directly. But for that specific issue we have the FROM_UNIXTIME() function: And again, this is given in the computer’s local time.
Which is fine, because it’s intended to be read by humans.
There are SQL functions to convert timezones, of course. What happens when you want to move your database to a server in another timezone? 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. Has 24 hours elapsed since the last warning email was sent? “Solution”: The SQL language supplies a variety of COBOL-like functions to calculate whatever we can ask for.
Enjoy the database on what it’s best at: Storing and collecting information.
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. UTC_TIMESTAMP() In case you really want to be independant of the timezone I suggest NOT using NOW() as that is localtime. It makes no sense saying that the webserver is half a globe away and still use NOW in those examples.
While I still stand behind every word I said, in particular for web applications (which I believe is the vast majority of My SQL use), the comments below make some valid points, and single out cases where DATETIME actually is the right thing.
The Java Script 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.
Maybe you know this, maybe not but it might be worth mentioning as you focus on timezones. 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.