Skip to content

How-to

How to Read PostgreSQL and MySQL Timestamps in Your Time Zone

Turn PostgreSQL timestamptz, MySQL DATETIME and SQL Server values into your local time, and know which carry a time zone and which only a wall clock.

The problem

“I pulled created_at from the database as 2024-06-29 13:45:07.123456+00. Is that my time or UTC, and what time was it in New York?”

Why database times are confusing

Databases have two kinds of date-time column, and they print almost the same text:

  • A moment in time. PostgreSQL timestamptz (timestamp with time zone) and MySQL TIMESTAMP are stored as UTC and printed in the session’s time zone; PostgreSQL adds the offset: 2024-06-29 13:45:07.123456+00. SQL Server datetimeoffset keeps the offset it was given and prints it.
  • A wall clock with no zone: PostgreSQL timestamp (without time zone), MySQL DATETIME, SQL Server datetime2. It stores the digits it was given. Whether 2024-06-29 13:45:07 was UTC, the server’s local time or the user’s depends on the application that wrote it.

So the same row can print differently in two tools, because each tool’s session has its own time zone, and a value without an offset can be off by hours with nothing in the data to show it. Precision differs too: PostgreSQL keeps microseconds; MySQL keeps whole seconds unless the column is declared with fractions, up to microseconds with DATETIME(6); SQL Server datetime2 keeps seven decimal places.

Fix it on Date And Time Converter

  1. Copy the value as the database tool prints it and paste it into the converter. It reads PostgreSQL’s short offset (+00, +05:30), the SQL datetime layout YYYY-MM-DD HH:mm:ss, and fractions of a second.
  2. If the value has an offset, nothing else is needed: choose your zone as the target. If it has none, set the source zone to the zone the application wrote it in. With no source zone the converter reads it as UTC and says so beside the value.
  3. Pick the target format: YYYY-MM-DD HH:mm:ss to compare with other database values, ISO 8601 with an offset to hand it to an API.
Worked example: A PostgreSQL timestamptz printed in UTC, as New York time.
Input
2024-06-29 13:45:07.123456+00 read as YYYY-MM-DD HH:mm:ss.SSS±hh:mm (detected)
Result
2024-06-29 09:45:07 YYYY-MM-DD HH:mm:ss · YYYY-MM-DD HH:mm:ss · EDT

Open in the converter: 2024-06-29 13:45:07.123456+00 → 2024-06-29 09:45:07

Worked example: A MySQL DATETIME the application wrote in Paris time, as UTC. You supply the zone.
Input
2024-06-29 13:45:07 read as YYYY-MM-DD HH:mm:ss (detected), in Paris
Result
2024-06-29 11:45:07 YYYY-MM-DD HH:mm:ss · YYYY-MM-DD HH:mm:ss

Open in the converter: 2024-06-29 13:45:07 → 2024-06-29 11:45:07

In SQL

-- PostgreSQL: a timestamptz as New York wall time
SELECT created_at AT TIME ZONE 'America/New_York' FROM orders;

-- PostgreSQL: print every timestamptz in one zone for this session
SET TIME ZONE 'America/New_York';

-- MySQL: a DATETIME written in UTC, shown in New York
-- (named zones need the time zone tables loaded)
SELECT CONVERT_TZ(created_at, '+00:00', 'America/New_York') FROM orders;

-- SQL Server 2016+: a datetime2 written in UTC, shown in New York
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' FROM orders;

In SQL Server, 'Eastern Standard Time' is the Windows name of the whole New York zone, daylight saving included, not the winter offset alone.

SQLite has no date type: dates are stored as ISO 8601 text, Unix seconds or Julian day numbers, and datetime(col, 'unixepoch') or datetime(col) turns them into UTC text.

Pitfalls

  • Two tools, two answers. A desktop SQL client and the application may run with different session zones and print the same timestamptz differently. Compare the offsets, not the clock digits.
  • AT TIME ZONE both ways. In PostgreSQL, applied to a timestamptz it gives the local wall time; applied to a timestamp it does the opposite and treats the value as local time in that zone. Know which type the column has.
  • MySQL TIMESTAMP limits. It only holds 1970 to 2038 and converts through the session zone; a server whose zone changed between writing and reading shifts every value.
  • Migrations. Moving a DATETIME column into a timestamptz one makes the database assume a zone for every row. Set it on purpose, or the whole history moves.

Questions

Why does PostgreSQL print +00 instead of +00:00?

PostgreSQL prints the offset the session’s time zone had at that moment, and leaves out the minutes when they are zero. +00 means the value is shown at offset zero, which is UTC: a session set to London also prints +00 in winter. +05:30 keeps its minutes.

Is a MySQL DATETIME in UTC?

Only if the application wrote UTC. DATETIME stores the clock time it was given with no zone. A MySQL TIMESTAMP column is converted from the session zone to UTC when stored and back when read.

What happens to microseconds?

The converter reads them and keeps the time to the millisecond. Digits after the third are dropped.