PostgreSQL to SQL Field Notes: Date & Time

Welcome to SQL

First, welcome to SQL! You’re going to like this.

Upgrading from PostgreSQL is real work, and depending on your old PostgreSQL, that process can vary widely in complexity. You’re going to love the mindful and modern features of SQL. Take your time, make the most of the resources built to help you in Microsoft Learn, and know there is a huge community of SQL developers ready to help along the way.

This blog is for the PG developer.

The SQL standard has helped align the shape of the two engines, and that familiarity will help you get up to speed quickly. However, there are differences, and there aren’t always equivalents. To help fast-track your learning, this series will highlight some differences worth knowing. Although some may surprise you, I believe most will be a delight.

Handling date and time

Let’s start with an everyday feature: handling date and time. In PostgreSQL, you have the timestamp and timestamptz types.

PostgreSQL timestamp

In PG, you use the timestamp type to store date and time values without any time zone information.

Using timestamp

SELECT TIMESTAMP '2026-09-21 10:30:00';

The value of timestamp in this case is stored exactly as provided. No time zone information is provided, and none is stored.

SELECT TIMESTAMP '2026-09-21 10:30:00-06:00';

If you include a time zone offset, timestamp simply ignores it. Even though you provided the -06:00 offset from UTC, that information is discarded. This behavior remains documented in PostgreSQL today.

If your application needed to preserve the original time zone or offset, you had to store that information separately.

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name TEXT NOT NULL,
    event_timestamp TIMESTAMP NOT NULL,
    event_timezone TEXT NOT NULL
);

This works, but now two values describe one event. Your application is responsible for keeping them together and interpreting them correctly.

SQL’s closest equivalent: datetime2

SQL’s closest equivalent to PostgreSQL timestamp is datetime2. SQL’s datetime2 is the modern replacement for SQL Server’s legacy datetime type. Microsoft recommends datetime2 for new development.

If your use case demands higher precision for scientific instruments, streaming data, or financial applications, SQL’s datetime2 means you no longer need to compromise on precision.

PostgreSQL timestamptz

PostgreSQL 7.2 established the modern distinction between timestamp without time zone and timestamp with time zone, commonly written today using PostgreSQL’s timestamptz shorthand.

SELECT TIMESTAMPTZ '2026-09-21 10:30:00-06:00';

This time, the offset matters. PostgreSQL uses it to determine the actual instant in time and stores that instant internally in UTC.

Unfortunately, you already know the caveat

PostgreSQL does not retain the original time zone or offset. When the value is retrieved, PostgreSQL converts it to the session’s current TimeZone.

So the same stored value can look different depending on who asks for it:

SET TIME ZONE 'America/Denver';

SELECT TIMESTAMPTZ '2026-09-21 10:30:00-06:00';
-- 2026-09-21 10:30:00-06

SET TIME ZONE 'America/New_York';

SELECT TIMESTAMPTZ '2026-09-21 10:30:00-06:00';
-- 2026-09-21 12:30:00-04

Both results represent exactly the same instant. That’s intentional PostgreSQL behavior. But if your application needs to know that the original value was 10:30 -06:00, timestamptz cannot tell you. That information is lost during the insert.

It can be frustrating. Application developers who need the original offset are left to handle it manually. The offset has to be stored separately rather than embedded in the value itself. The value of timestamptz is that PostgreSQL normalizes timestamps to a common instant and automatically converts them to the session’s configured time zone when retrieved.

Good news! You’re using SQL now.

Introducing SQL’s datetimeoffset type. When time zone information is important, datetimeoffset allows you to store both the local date and time as well as the offset from UTC, preserving the original context of the timestamp.

DECLARE @dt DATETIMEOFFSET = '2026-09-21 10:30:00-06:00';

This advanced type has been around since SQL Server 2008. Ubiquitous in modern SQL Server applications, it provides a flexible way to handle time zone information.

Here’s how it works

The datetimeoffset type stores both the local date and time as well as the offset from UTC. This means that when you insert a value like '2026-09-21 10:30:00-06:00', SQL Server retains the -06:00 offset along with the date and time, preserving the original context of the timestamp.

Do you want to show it as UTC? You have two easy options:

SELECT @dt AT TIME ZONE 'UTC';

SELECT SWITCHOFFSET(@dt, '+00:00');

By the way, that same ultra-high precision you enjoy with SQL’s datetime2 is available with datetimeoffset, but with the added benefit of time zone awareness.

-- Example showing high precision with datetimeoffset

DECLARE @dtHighPrecision DATETIMEOFFSET(7) =
    '2026-09-21 10:30:00.1234567-06:00';

SELECT @dtHighPrecision;

Storage differences

datetimeoffset requires 8 to 10 bytes of storage, depending on fractional-second precision. This is slightly more than datetime2, which requires 6 to 8 bytes. The additional 2 bytes store the time zone offset.

Consider the impact of this additional storage across a billion rows. Attending to storage doesn’t just save disk space; it can also reduce I/O, memory pressure, and index size.

That’s a 2 GB difference in storage for a billion rows. Since dates are so common in many applications, this difference can have a significant impact on overall storage requirements and performance.

Your default type for dates

Because of this storage difference, your default should not always be datetimeoffset, especially when your app doesn’t care about the time zone. In many cases, datetime2 remains an excellent choice.

Having said that, since precision is variable, you can also balance storage requirements with the need for time zone awareness by carefully choosing the appropriate precision for your application.

That’s a 2 GB difference in storage for a billion rows when choosing between the lowest and highest precision for datetimeoffset. What’s more, you still have time zone support when you need it.

Congratulations

Let me welcome you again to SQL! I hope it’s clear that you’ve joined a rich and thoughtful ecosystem. Choosing the right date and time type for your application is important, and SQL helps make that easier, simpler, and better.

Happy querying!