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

Isometric illustration of a database beside two clocks joined by a blue arc, showing time-zone conversion

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.

TypeWhat it storesGood for
TIMESTAMPA moment, kept as UTC and shown in the session time zone“Created at”, “updated at”
DATETIMEA date and time exactly as written, with no time zoneAppointments in a known local time
INT / BIGINTRaw Unix secondsData 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.

Back to the blog