A Unix timestamp is the number of seconds since January 1, 1970 at 00:00:00 UTC. It identifies one moment in time, but it does not store a time zone.
This mini-course starts with simple conversions. It then covers storing timestamps, finding records in a date range, grouping results, time zones, milliseconds, and dates before 1970.
Quick summary
| Goal | MySQL query |
|---|---|
| Get the current epoch time | SELECT UNIX_TIMESTAMP(); |
| Get midnight at the start of today | SELECT UNIX_TIMESTAMP(CURDATE()); |
| Get midnight at the start of yesterday | SELECT UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 DAY); |
| Get January 1 of the current year | SELECT UNIX_TIMESTAMP(MAKEDATE(YEAR(CURDATE()), 1)); |
| Convert a date and time to epoch time | SELECT UNIX_TIMESTAMP('2026-07-15 12:00:00'); |
| Convert epoch time to a date and time | SELECT FROM_UNIXTIME(1784116800); |
The date-based queries in this table use the current MySQL session time zone. The next section explains why that matters.
Time zones
FROM_UNIXTIME() displays a timestamp in the current MySQL session time zone. In the other direction, UNIX_TIMESTAMP(date) assumes that the supplied date and time are in that same time zone.
Check the current setting with:
SELECT @@session.time_zone;The default MySQL setting may already be correct for an application. There is no need to change it just because this page uses UTC. The important point is to know which time zone is in use and to use it consistently.
The examples below set the session to UTC so that they give the same results on every server:
SET time_zone = '+00:00';This setting lasts for the current database connection. An application that needs UTC should normally set it when opening each connection.
Choosing how to store a date and time
MySQL offers several useful choices:
| Type | When it is useful |
|---|---|
DATETIME | Stores a date and time as written. It supports a wide range of years, but does not convert between time zones. |
TIMESTAMP | Converts between the session time zone and UTC. Its range ends in January 2038. |
BIGINT | Stores a raw Unix timestamp. This is useful when other systems already send or expect epoch values. |
For a new database, DATETIME or TIMESTAMP may be clearer than a raw number. This course uses BIGINT because its subject is working directly with Unix timestamps.
Create an example table
The table records visits to pages. BIGINT avoids the 2038 limit of a signed INT, and also allows negative epoch values. The index makes time-range searches faster as the table grows.
CREATE TABLE visits (
visit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
url VARCHAR(255) NOT NULL,
visited_epoch BIGINT NOT NULL,
PRIMARY KEY (visit_id),
INDEX idx_visited_epoch (visited_epoch)
);
INSERT INTO visits (user_id, url, visited_epoch) VALUES
(1, 'homepage', 1793534400),
(2, 'contact', 1793880000),
(3, 'contact', 1793966400),
(4, 'homepage', 1796126400);These four timestamps represent noon UTC on November 1, November 5, November 6, and December 1 of 2026.
Convert epoch time to readable dates
SELECT
user_id,
url,
FROM_UNIXTIME(visited_epoch) AS visited_at
FROM visits
ORDER BY visited_epoch;Because the session time zone was set to UTC, the result is:
1 homepage 2026-11-01 12:00:00 2 contact 2026-11-05 12:00:00 3 contact 2026-11-06 12:00:00 4 homepage 2026-12-01 12:00:00
Add a format when only part of the date is needed:
SELECT
user_id,
url,
FROM_UNIXTIME(visited_epoch, '%Y-%m-%d') AS visit_date
FROM visits
ORDER BY visited_epoch;This returns:
1 homepage 2026-11-01 2 contact 2026-11-05 3 contact 2026-11-06 4 homepage 2026-12-01
Convert a date to epoch time
Use UNIX_TIMESTAMP() without a value to get the current time:
SELECT UNIX_TIMESTAMP();Supply a date and time to convert a particular value. MySQL reads it in the current session time zone:
SELECT UNIX_TIMESTAMP('2026-12-01 12:00:00');With the session set to UTC, this returns 1796126400.
Add a new record
Use UNIX_TIMESTAMP() to store the current time:
INSERT INTO visits (user_id, url, visited_epoch)
VALUES (1, 'news', UNIX_TIMESTAMP());Or convert a known date and time while inserting it:
INSERT INTO visits (user_id, url, visited_epoch)
VALUES (1, 'news', UNIX_TIMESTAMP('2026-07-15 12:00:00'));Find records in a date range
This query finds all visits in November 2026:
SELECT user_id, url, FROM_UNIXTIME(visited_epoch) AS visited_at
FROM visits
WHERE visited_epoch >= UNIX_TIMESTAMP('2026-11-01 00:00:00')
AND visited_epoch < UNIX_TIMESTAMP('2026-12-01 00:00:00')
ORDER BY visited_epoch;The query includes the start of November and stops just before the start of December. This pattern works cleanly even when values include hours, minutes, seconds, or fractions of a second. It can also use the index on visited_epoch.
Group timestamps by month
The following query counts visits in each month. The %Y-%m format sorts correctly because the year comes first:
SELECT
FROM_UNIXTIME(visited_epoch, '%Y-%m') AS visit_month,
COUNT(*) AS visit_count
FROM visits
GROUP BY visit_month
ORDER BY visit_month;The result is:
2026-11 3 2026-12 1
Change the format to group the same records in other ways:
| Group by | Format | Example |
|---|---|---|
| Year | %Y | 2026 |
| Month | %Y-%m | 2026-11 |
| Day | %Y-%m-%d | 2026-11-01 |
| Hour | %Y-%m-%d %H:00 | 2026-11-01 12:00 |
Grouping follows the current MySQL session time zone. A timestamp near midnight may therefore belong to a different day or month after changing the time zone.
Seconds and milliseconds
Unix timestamps normally use seconds. Some systems, including JavaScript, often use milliseconds and produce a 13-digit value.
Divide milliseconds by 1000 before passing the value to FROM_UNIXTIME():
SELECT FROM_UNIXTIME(1784116800000 / 1000);Multiply by 1000 when an external system needs milliseconds:
SELECT UNIX_TIMESTAMP() * 1000;Decide whether a column stores seconds or milliseconds, document that choice, and do not mix the two.
Dates before 1970
FROM_UNIXTIME() does not handle negative epoch timestamps. MySQL can still store dates before 1970 in a DATE or DATETIME column, and date calculations provide a workaround for raw epoch values.
Convert a negative epoch value to a UTC date and time:
SELECT TIMESTAMPADD(
SECOND,
-315619200,
'1970-01-01 00:00:00'
);This returns 1960-01-01 00:00:00.
Convert a UTC date before 1970 to epoch seconds:
SELECT TIMESTAMPDIFF(
SECOND,
'1970-01-01 00:00:00',
'1960-01-01 00:00:00'
);This returns -315619200. These calculations treat the written date and time as UTC. If the source date is local time, convert it to UTC first.
The year 2038
A signed INT cannot store epoch seconds after January 19, 2038. A TIMESTAMP column also ends in 2038. Use BIGINT for raw epoch values that must go beyond that date, or use DATETIME when storing a normal MySQL date and time. Read more about the year 2038 problem.
MySQL's date functions have their own supported ranges, so check unusually old or far-future dates before saving them.
Common date format codes
| Code | Description | Example |
|---|---|---|
| %Y | Four-digit year | 2026 |
| %m | Month, two digits | 07 |
| %d | Day of the month, two digits | 15 |
| %H | Hour, from 00 to 23 | 12 |
| %i | Minutes | 30 |
| %s | Seconds | 45 |
| %f | Microseconds | 123456 |
| %W | Full weekday name | Wednesday |
| %M | Full month name | July |
| %% | A percent character | % |