MySQL Queries ·
MySQL Unix Timestamps and Time Zones (UTC and IST)
By Manu Mathew, Technical Lead Engineer · Published 4 October 2026 · Last updated 5 October 2026

Times in a database go wrong quietly. A report is off by five and a half hours, or a reminder fires at the wrong time, because one query assumed the server’s time zone. This page brings together several short notes I wrote in 2013 on the MySQL Unix timestamp and time zones. I have rewritten them in one place, with tested examples and current advice, so you don’t have to piece the answer together from fragments.
Quick answer
UNIX_TIMESTAMP() returns the current Unix time, which is always counted from UTC. FROM_UNIXTIME(ts) turns a Unix time into a date and time in the session time zone. To show IST, set time_zone = '+05:30' for the session or use CONVERT_TZ(), and use UTC_TIMESTAMP() for the current UTC time.
I ran every query below on MariaDB 11.8; MySQL 8.0 behaves the same. The examples use the fixed timestamp 1380030402, which is 24 September 2013, 13:46:42 UTC.
How do I get the current Unix timestamp?
SELECT UNIX_TIMESTAMP() returns the number of seconds since 1970-01-01 00:00:00 UTC.
SELECT UNIX_TIMESTAMP(); -- seconds since 1970-01-01 00:00:00 UTC
Because a Unix timestamp is counted from UTC, it is the same number everywhere in the world at a given moment. That is what makes it a good format to store and compare.
How do I turn a Unix timestamp into a readable date?
FROM_UNIXTIME(ts) gives YYYY-MM-DD HH:MM:SS in the session time zone; add a format string for other layouts.
SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1380030402); -- 2013-09-24 13:46:42
SELECT FROM_UNIXTIME(1380030402, '%d/%m/%Y %H:%i'); -- 24/09/2013 13:46
The format codes follow DATE_FORMAT(): %d day, %m month, %Y four-digit year, %H hour (00–23) and %i minutes.
How do I show a Unix timestamp in IST?
Either set the session time zone to +05:30, or convert from UTC to +05:30 with CONVERT_TZ().
-- Option 1: set the session time zone, then convert
SET time_zone = '+05:30';
SELECT FROM_UNIXTIME(1380030402); -- 2013-09-24 19:16:42
-- Option 2: convert explicitly from UTC to IST
SET time_zone = '+00:00';
SELECT CONVERT_TZ(FROM_UNIXTIME(1380030402), '+00:00', '+05:30'); -- 2013-09-24 19:16:42
The direction matters. CONVERT_TZ(value, from, to) assumes value is in the from zone. An earlier version of this note converted from +05:30 to +00:00, which moves the time the wrong way: on a server set to IST it printed UTC instead of IST, and on a UTC server the result is 11 hours off. Named zones such as 'Asia/Kolkata' also work once the server’s time zone tables are loaded.
How do I get the current time in UTC?
UTC_TIMESTAMP() returns the current UTC date and time whatever the session time zone; NOW() uses the session time zone.
SELECT UTC_TIMESTAMP(); -- current date and time in UTC, whatever the session time zone
SELECT NOW(); -- current date and time in the session time zone
You don’t need to convert before calling UNIX_TIMESTAMP(). Two older notes on this site did UNIX_TIMESTAMP(CONVERT_TZ(CURRENT_TIMESTAMP, '+05:30', '+00:00')); on a server in IST that returns a value 19,800 seconds (5½ hours) too small, because UNIX_TIMESTAMP() already works in UTC. This query shows that the same moment gives the same timestamp in either zone:
SET time_zone = '+05:30';
SELECT UNIX_TIMESTAMP('2013-09-24 19:16:42'); -- 1380030402
SET time_zone = '+00:00';
SELECT UNIX_TIMESTAMP('2013-09-24 13:46:42'); -- 1380030402
Should I store times as TIMESTAMP, DATETIME or an integer?
Use TIMESTAMP or DATETIME for most tables, and choose based on whether the value is a moment or a local wall-clock time.
| Type | What it stores | Good for |
|---|---|---|
TIMESTAMP | A moment, kept as UTC and shown in the session time zone | “Created at”, “updated at” |
DATETIME | A date and time exactly as written, with no time zone | Appointments in a known local time |
INT / BIGINT | Raw Unix seconds | Data shared with systems that expect Unix time |
CREATE TABLE events (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
starts_at DATETIME NOT NULL
);
INSERT INTO events (starts_at) VALUES ('2026-10-05 10:00:00');
SELECT id, UNIX_TIMESTAMP(created_at) AS created_unix, starts_at FROM events;
In MySQL and in MariaDB before 11.5, TIMESTAMP stops at 2038-01-19 03:14:07 UTC. For dates beyond that, use DATETIME or a BIGINT.
Frequently asked questions
Does a Unix timestamp include a time zone?
No. A Unix timestamp is a count of seconds since 1 January 1970 UTC, so no zone is attached to it. The zone only matters when you turn it into a readable date, which is why FROM_UNIXTIME() and NOW() depend on the session time_zone setting.
Why is my time five and a half hours out?
Usually one step treated a UTC value as IST, or the other way round. Check the session zone with SELECT @@session.time_zone, store times in UTC, and convert to +05:30 only when you display them. A conversion applied twice, once in SQL and once in the application, is the other common cause.
How do I use named zones such as Asia/Kolkata?
Load the time zone tables first. On Linux, mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql loads them; MariaDB ships the same tool as mariadb-tzinfo-to-sql. After that, SET time_zone = 'Asia/Kolkata' and CONVERT_TZ() with named zones work as expected.
Can I convert the timestamp in PHP or Perl instead of SQL?
Yes. In PHP, (new DateTimeImmutable('@1380030402'))->setTimezone(new DateTimeZone('Asia/Kolkata')) gives 19:16:42 IST. In Perl, gmtime(1380030402) gives the UTC time, and the DateTime module can show any named zone. Converting in the application is fine; just do it in one place.
Related notes: MySQL query notes; dates in Perl; small PHP snippets.
Need help with Perl code?
If you have Perl code that needs fixing, extending or upgrading, I’m happy to take a look. I work remotely from Kozhikode, Kerala, with teams across India and elsewhere; Perl development and maintenance explains how I help.
You can reach me through the contact section or email me at manu@manu.co.in. A few lines about the system and what you need is a good start.
Sources: MySQL 8.0: date and time functions; MySQL 8.0: time zone support; MySQL 8.0: DATE, DATETIME and TIMESTAMP; MariaDB: TIMESTAMP.