By Bryan Young
Expert Author
Article Date: 2010-11-09
In the databases I have used, I have found two major schools of thought when it comes to storing dates in tables. There will either be a string like date, listed out in year-month-day hour-minute-second format, or there will be a timestamp. For those who don't know, a timestamp is usually just the number of seconds since the Unix epoch (00:00:00 UTC, January 1, 1970). One big advantage is that the timestamp is the same regardless of timezone or daylight savings time. Let's look at how we can manipulate timestamps to our advantage.
To see what time it is now, use the NOW() function
SELECT NOW();
This outputs a long format date that represents the date and time of the system clock. To see what the timestamp is, we use the UNIX_TIMESTAMP function.
SELECT UNIX_TIMESTAMP( NOW() );
We can now use this to our advantage. If we want to see how many events have been logged in the database in the past 24 hours, we say
SELECT event FROM log WHERE timestamp > ( UNIX_TIMESTAMP( NOW() ) - 86400 );
This takes the timestamp from NOW() and subtracts 86400 seconds (24 hours). Let's look at the events from November 1st, 2010.
SELECT event FROM log WHERE timestamp BETWEEN UNIX_TIMESTAMP( '2010-11-1' ) AND UNIX_TIMESTAMP( '2010-11-2');
This returns all the events that happen between midnight on November 1st and 2nd. In order to get a readable format from a timestamp, we use the FROM_UNIXTIME() function.
SELECT event, FROM_UNIXTIME( timestamp ) FROM log WHERE timestamp BETWEEN UNIX_TIMESTAMP( '2010-11-1' ) AND UNIX_TIMESTAMP( '2010-11-2');
Between the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions, you now have everything you need to manipulate time and display your database contents in an organized fashion.
