Using Unix Timestamps in MySQL Mini-Course

This page gives you information on how to easily use Unix Timestamps in MySQL.

Quick summary

Get current epoch time	SELECT UNIX_TIMESTAMP(NOW()) (now() is optional)
Today midnight	SELECT UNIX_TIMESTAMP(CURDATE())
Yesterday midnight	SELECT UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -1 DAY))
Convert from date to epoch	SELECT UNIX_TIMESTAMP(timestring) Time format: YYYY-MM-DD HH:MM:SS or YYMMDD or YYYYMMDD
Convert from epoch to date	SELECT FROM_UNIXTIME(epoch timestamp, optional output format) The default output is YYY-MM-DD HH:MM:SS FROM_UNIXTIME doesn't work with negative timestamps

The Mini-Course

Let's first create a simple logging-table and some sample records.
All queries on this page will work with the following table.

-- Table "mytable" DDL

CREATE TABLE `mytable` (
  `userId` int(11) NOT NULL,
  `url` varchar(100) NOT NULL,
  `epoch` int(11) NOT NULL
);

INSERT INTO mytable VALUES(1,'homepage',1225540800); 
# User 1 visited the url homepage on Nov 1, 2008
INSERT INTO mytable VALUES(2,'contact',1225886400); 
# User 2 visited the url contact on Nov 5, 2008
INSERT INTO mytable VALUES(3,'contact',1225972800); 
# User 3 visited the url contact on Nov 6, 2008
INSERT INTO mytable VALUES(4,'homepage',1228132800); 
# User 4 visited the url homepage on Dec 1, 2008

Converting to readable dates

SELECT userId, url, FROM_UNIXTIME(epoch) FROM mytable

This query outputs:

1   homepage   2008-11-01 13:00:00
2   contact   2008-11-05 13:00:00
3   contact   2008-11-06 13:00:00
4   homepage   2008-12-01 13:00:00

You can format your date by using specifiers (look below for a list of specifiers).

SELECT userId, url, FROM_UNIXTIME(epoch,"%Y-%m-%d") FROM mytable

Output:

1   homepage   2008-11-01
2   contact   2008-11-05
3   contact   2008-11-06
4   homepage   2008-12-01

Grouping Epochs

Let's say you want to get statistics by month. This query groups months, and counts the number of visitor (records) in each month. We order by epoch to get all results in the right order.

SELECT DISTINCT FROM_UNIXTIME(epoch,"%M, %Y") AS month, count(*) as numberOfVisits
FROM mytable
GROUP BY month
ORDER BY epoch

This outputs:

November, 2008   3
December, 2008   1

This query can be easily changed to get statistics per year, per day, per day of the week, per hour of the day etc. For example, to get yearly stats change the query to:

SELECT DISTINCT FROM_UNIXTIME(epoch,"%Y") AS year, count(*) as numberOfVisits
FROM mytable
GROUP BY year
ORDER BY epoch

Adding a new record to our database

Use the UNIX_TIMESTAMP() function to convert MySQL dates/times (such as now() = current time) to epochs.

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP(now()))

or use YYYY-MM-DD HH:MM:SS :

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP('2008-12-01 12:00:00'))

Negative Epochs

There's one big problem with MySQL: MySQL cannot convert negative epoch timestamps (dates before 1-1-1970). This creates problems with for example birthdates. But there are workarounds.

When converting from epoch to human readable date use the DATE_ADD function:

# converting to MySQL date:
SELECT DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second);
# converting your epoch to a date string:
SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d');

Where -315619200 is your negative epoch. This query returns: 1960-01-01 01:00:00

When converting normal dates to epoch use TIMESTAMPDIFF:

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );

Replace the 1960 date with your date in your local timezone (MySQL time_zone).

MySQL date format specifiers

Specifier	Description
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with %X
%v	Week (01..53), where Monday is the first day of the week; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal '%' character

Click here for more information on MySQL.com

Comments and questions

blog comments powered by Disqus

« Epoch Converter Functions