Skip to main content

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


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:
In Python:
from time import time;
print time();
(note that the time is returned as a float number with higher precision)
In C:
#include 
#include 

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

  printf("%d seconds since the Epoch\n", now);
  return 0;
}
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.

Comments

Popular posts from this blog

Shell Script to check tomcat status and restart

The below script checks the status of a particular tomcat status and restarts it if the tomcat does not respond....TOMCAT_HOME=/usr/local/tomcat-folder/

is_Running ()
{

        wget -O - http://yourserver.com/ >& /dev/null
 if( test $? -eq 0 ) then
  return 0
 else
  return 1
 fi
}


kill_Hanged_Processes ()
{
 echo "killing hanged processes......"
 javaProcs=`ps -efl| grep -v grep | grep java`
 if(test ! -z "$javaProcs") then
  echo "nonzero"
  processId=`echo $javaProcs | awk '{ print $2} '`
  echo "$processId"
  kill -9 $processId
 fi
}
stop_Tomcat ()
{
 echo "shutting down......"
 $TOMCAT_HOME/bin/shutdown.sh
}

start_Tomcat ()
{
 echo "starting......"
 $TOMCAT_HOME/bin/startup.sh
}

restart ()
{
 stop_Tomcat
 sleep 10
 kill_Hanged_Processes
 start_Tomcat
 sleep 60
}

send_Mail ()
{
#!/bin/bash
# script to send simple email
# email subject
SUBJECT="Telegraphindia.com went down"
# Email To ?
EMAIL="mailid1@gmail.com,mailid2@gmail.com"
# Em…

Getting access to menu from portlet in liferay

The below code describes the way of accessing menu items from the portlet. Here i have called it from jsp. The best thing about this is you can use the beautiful permission management of menu items of liferay as well without bothering about that. Writing services for fetching menu names from the layout tables will need more codes for permission managements......

Hope this helps.........


<%--
/**
* Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
*
* This library is free software; you can redistribute it and/or modify it under
* the terms of the GNU Lesser General Public License as published by the Free
* Software Foundation; either version 2.1 of the License, or (at your option)
* any later version.
*
* This library is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
* FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
* details.
*/
--%>
<%@ page import="com.li…

Virus f Opyum Team

Symptoms :: 
Several processes running named f or i. 
Network choked
Apache Dead
Unknown entry  * * * * * root f Opyum Team in crontab

Investigate ::
Try to kill the f process and even root will be unable to kill it. Check file named f and i in /etc or /bin or /usr/sbin directory. Try to delete, the file f  will not delete.  Some unknown .jpg files will be there in the same folder. 

Solve :: The root is unable to delete the file f because of the immutable bit in f
To remove it use ses or chattr
Follow the below steps to remove it completely.....



remove the unknown .jpg files in system folders

#top
(kill process f) option k

#ses -i /bin/f
#rm /bin/f
#ses -i /etc/crontab
#vi /etc/crontab (and delete last line)
#reboot


Precautions ::  In my case the server was hacked bu Brute-force attack
So set a good password with a combination of upper,lower case letters and special characters.

Post your feedback or any issues you may face removing the virus.  Very less number of good threads are there explaining …