Unix timestamp or datetime mysql

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

Quick summary

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 visitors (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

MySQL 8.0 info in more detail

« Epoch Converter Functions

Should I use datetime or TIMESTAMP in MySQL?

Should I use TIMESTAMP or datetime?

What is the difference between Unix timestamp and MySQL TIMESTAMP?

What is Unix timestamp in MySQL?