Using Unix Timestamps in MySQL

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

Quick summary

Goal	MySQL query
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))
Jan 1 of current year	SELECT UNIX_TIMESTAMP(CONCAT(YEAR(CURDATE()),'-01-01'))
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 YYYY-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 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

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

MySQL 8.0 info in more detail

MySQL DATEDIFF() vs TIMESTAMPDIFF(): What’s the Difference?

This article looks at the difference between two MySQL functions; DATEDIFF() and TIMESTAMPDIFF().

Both functions return the difference between two dates and/or times, but the result is different between the two functions.

The following table summarizes the difference between these two functions:

DATEDIFF()	TIMESTAMPDIFF()
Requires 2 arguments.	Requires 3 arguments.
Subtracts the 2nd argument from the 1st (expr1 − expr2).	Subtracts the 2nd argument from the 3rd (expr2 − expr1).
Result is expressed as a value in days.	Result is expressed as the unit provided by the first argument.
Can compare only the date value of its arguments.	Can compare the date and time value of its arguments.

Example 1 – Basic Operation

Here’s an example that demonstrates how these functions work, and how the results are different, even when using the same unit.

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |            -1 |
+----------+---------------+

So both functions return the difference in days, however one result is positive and the other negative. This is because DATEDIFF() subtracts the second date from the first, whereas TIMESTAMPDIFF() subtracts the first date from the second.

Example 2 – Changing the Unit

As the previous example demonstrates, the TIMESTAMPDIFF() allows you to specify a unit for the results to be returned as (in fact, it requires you to specify the unit). On the other hand, DATEDIFF() doesn’t allow you to specify a unit. It only returns the result in days.

So we could modify the previous example so that TIMESTAMPDIFF() returns the number of hours instead of days:

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |           -24 |
+----------+---------------+

You can go all the way to microseconds:

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |  -86400000000 |
+----------+---------------+

Example 3 – Precision

The precision of DATEDIFF() is one day, and TIMESTAMPDIFF() can go down to the microsecond. However the precision of TIMESTAMPDIFF() (and the unit that it compares) still depends on the specified unit.

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-10-10 23:59:59';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'Microseconds';

Result:

+----------+------+-------+---------+---------+--------------+
| DATEDIFF | Days | Hours | Minutes | Seconds | Microseconds |
+----------+------+-------+---------+---------+--------------+
|        0 |    0 |    23 |    1439 |   86399 |  86399000000 |
+----------+------+-------+---------+---------+--------------+

And here’s the result if we increment the 2nd date by one second (which brings it to the next day):

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-10-11 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds',
  TIMESTAMPDIFF(microsecond, @date1, @date2) AS 'Microseconds';

Result:

+----------+------+-------+---------+---------+--------------+
| DATEDIFF | Days | Hours | Minutes | Seconds | Microseconds |
+----------+------+-------+---------+---------+--------------+
|       -1 |    1 |    24 |    1440 |   86400 |  86400000000 |
+----------+------+-------+---------+---------+--------------+

Here’s another example, this time seeing how it looks when we return months, quarters, and years when the difference is one month (or 31 days):

SET @date1 = '2010-10-10 00:00:00', @date2 = '2010-11-10 00:00:00';
SELECT 
  DATEDIFF(@date1, @date2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(month, @date1, @date2) AS 'Month',
  TIMESTAMPDIFF(quarter, @date1, @date2) AS 'Quarter',
  TIMESTAMPDIFF(year, @date1, @date2) AS 'Year';

Result:

+----------+------+-------+---------+------+
| DATEDIFF | Days | Month | Quarter | Year |
+----------+------+-------+---------+------+
|      -31 |   31 |     1 |       0 |    0 |
+----------+------+-------+---------+------+

Example 4 – Wrong Argument Types

Both functions return null if they are passed the wrong argument type.

SET @time1 = '12:15:35', @time2 = '00:00:00';
SELECT 
  DATEDIFF(@time1, @time2) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @time1, @time2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|     NULL |          NULL |
+----------+---------------+

Example 5 – Mixed Argument Types

Both functions allow you to provide a date as one argument and a datetime as another argument.

SET @thedate = '2010-10-11', @thedatetime = '2010-10-10 00:00:00';
SELECT 
  DATEDIFF(@thedate, @thedatetime) AS 'DATEDIFF',
  TIMESTAMPDIFF(day, @thedate, @thedatetime) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| DATEDIFF | TIMESTAMPDIFF |
+----------+---------------+
|        1 |            -1 |
+----------+---------------+

MySQL TIMEDIFF() vs TIMESTAMPDIFF(): What’s the Difference?

This article looks at the difference between the MySQL TIMEDIFF() and TIMESTAMPDIFF() functions.

Both functions do a similar thing, but there are some significant differences between the two.

The following table summarizes the difference between these two functions:

TIMEDIFF()	TIMESTAMPDIFF()
Requires 2 arguments.	Requires 3 arguments.
Subtracts the 2nd argument from the 1st (date1 − date2).	Subtracts the 2nd argument from the 3rd (date2 − date1).
Result is expressed as a time value (and it has the limitations of the time data type).	Result is an integer, expressed by a number of units as provided by the first argument.
Accepts time or datetime expressions.	Accepts date or datetime expressions.
Both arguments must be the same type (either time or datetime).	Both arguments can be of a different type (date or datetime).

Example 1 – Basic Difference

Here’s an example that demonstrates the basic difference between these functions.

SET @date1 = '2010-10-11 00:00:00', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| TIMEDIFF | TIMESTAMPDIFF |
+----------+---------------+
| 24:00:00 |           -24 |
+----------+---------------+

So we can see that TIMEEDIFF() returned a time value, and TIMESTAMPDIFF() returned an integer.

Also, TIMEEDIFF() subtracted the 2nd date from the 1st, while TIMESTAMPDIFF()subtracted the 1st date from the 2nd.

Example 2 – Changing the Unit

As mentioned, TIMESTAMPDIFF() allows us to specify which unit to represent the result in. Here are some examples:

SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours',
  TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes',
  TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds';

Result:

+----------+-------+---------+---------+
| TIMEDIFF | Hours | Minutes | Seconds |
+----------+-------+---------+---------+
| 36:15:35 |   -36 |   -2175 | -130535 |
+----------+-------+---------+---------+

However, here’s what happens if we use a unit that’s larger than the actual time difference:

SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF',
  TIMESTAMPDIFF(day, @date1, @date2) AS 'Days',
  TIMESTAMPDIFF(week, @date1, @date2) AS 'Weeks',
  TIMESTAMPDIFF(month, @date1, @date2) AS 'Months';

Result:

+----------+------+-------+--------+
| TIMEDIFF | Days | Weeks | Months |
+----------+------+-------+--------+
| 36:15:35 |   -1 |     0 |      0 |
+----------+------+-------+--------+

In this case, the time difference wasn’t big enough to affect the week or month values.

Example 3 – Wrong Argument Types

Here’s an example of what happens when you pass the wrong argument types to each function.

SET @date1 = '2010-10-11', @date2 = '2010-10-10', @time1 = '12:15:35', @time2 = '00:00:00';
SELECT 
  TIMEDIFF(@date1, @date2) AS 'TIMEDIFF Date',
  TIMESTAMPDIFF(hour, @time1, @time2) AS 'TIMESTAMPDIFF Time';

Result:

+---------------+--------------------+
| TIMEDIFF Date | TIMESTAMPDIFF Time |
+---------------+--------------------+
| 00:00:00      |               NULL |
+---------------+--------------------+

The TIMEDIFF() doesn’t support the ‘date’ data type, and so it returns 00:00:00.

And the TIMESTAMPDIFF() function doesn’t support the ‘time’ data type, so it returns NULL.

Example 4 – Mixed Argument Types

Here’s what happens if you provide two different data types to each function.

SET @thedate = '2010-10-11', @thetime = '12:15:35', @thedatetime = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@thetime, @thedatetime) AS 'TIMEDIFF',
  TIMESTAMPDIFF(hour, @thedate, @thedatetime) AS 'TIMESTAMPDIFF';

Result:

+----------+---------------+
| TIMEDIFF | TIMESTAMPDIFF |
+----------+---------------+
| NULL     |           -24 |
+----------+---------------+

So we can see that TIMESTAMPDIFF() handles mixed data types fine (as long as they’re either date or date-and-time).

However, TIMEDIFF() requires that both arguments are of the same type, so we get NULL, even though both arguments are of a type that the function supports (time and datetime).

We can confirm that both types are in fact supported by this function with the following example:

SET @thetime1 = '12:15:35', @thetime2 = '10:15:35', @thedatetime1 = '2010-10-12 00:00:00', @thedatetime2 = '2010-10-10 00:00:00';
SELECT 
  TIMEDIFF(@thetime1, @thetime2) AS 'time',
  TIMEDIFF(@thedatetime1, @thedatetime2) AS 'datetime';

Result:

+----------+----------+
| time     | datetime |
+----------+----------+
| 02:00:00 | 48:00:00 |
+----------+----------+

So it’s fine, as long as both arguments are the same type (either time or datetime values).

MySQL - TIMESTAMPDIFF() Examples

The MySQL TIMESTAMPDIFF() function is used to find the difference between two date or datetime expressions. You need to pass in the two date/datetime values, as well as the unit to use in determining the difference (e.g., day, month, etc). The TIMESTAMPDIFF()function will then return the difference in the specified unit.

Syntax

First, here’s how the syntax goes:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Here, unit is the unit to use in expressing the difference (e.g. day, month, year, etc). datetime_expr1 is the first date/datetime value, and datetime_expr2 is the second.

This function subtracts datetime_expr1 from datetime_expr2 and returns the result in units. The result is returned as an integer.

Valid Units

The unit argument can be any of the following:

• MICROSECOND
•  SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

Example 1 – Difference in Days

Here’s an example to demonstrate the basic usage of this function. Here we compare two date expressions and return the difference between them in days.

SELECT 
  TIMESTAMPDIFF(DAY,'2022-02-01','2022-02-21')
  AS 'Difference in Days';

Result:

+--------------------+
| Difference in Days |
+--------------------+
|                 20 |
+--------------------+

Example 2 – Difference in Hours

In this example we compare the same values as in the previous example, except here, we return the difference in hours.

SELECT 
  TIMESTAMPDIFF(HOUR,'2022-02-01','2022-02-21')
  AS 'Difference in Hours';

Result:

+---------------------+
| Difference in Hours |
+---------------------+
|                 480 |
+---------------------+

Example 3 – A ‘datetime’ Example

Here’s an example that returns the difference in minutes. In this case, we compare two datetime values (as opposed to just the date values as in the previous examples).

SELECT 
  TIMESTAMPDIFF(MINUTE,'2022-02-01 10:30:27','2022-02-01 10:45:27')
  AS 'Difference in Minutes';

Result:

+-----------------------+
| Difference in Minutes |
+-----------------------+
|                    15 |
+-----------------------+

Example 4 – Fractional Seconds

You can go right down to the microsecond (6 digits) if you need to.

SELECT 
  TIMESTAMPDIFF(MICROSECOND,'2022-02-01 10:30:27.000000','2022-02-01 10:30:27.123456') 
  AS 'Difference in Microseconds';

Result:

+----------------------------+
| Difference in Microseconds |
+----------------------------+
|                     123456 |
+----------------------------+

Example 5 – Negative Results

As would be expected, if the first date/time argument is greater than the second, the result will be a negative integer.

SELECT 
  TIMESTAMPDIFF(DAY,'2022-02-21','2022-02-01')
  AS 'Difference in Days';

Result:

+--------------------+
| Difference in Days |
+--------------------+
|                -20 |
+--------------------+