{"id":7603,"date":"2026-09-21T15:44:58","date_gmt":"2026-09-21T22:44:58","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=7603"},"modified":"2026-09-24T06:42:14","modified_gmt":"2026-09-24T13:42:14","slug":"pg-to-sql-date-time","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/pg-to-sql-date-time\/","title":{"rendered":"PostgreSQL to SQL Field Notes: Date & Time"},"content":{"rendered":"

Welcome to SQL<\/h2>\n

First, welcome to SQL!<\/strong> You’re going to like this.<\/p>\n

Upgrading from PostgreSQL is real work, and depending on your old PostgreSQL, that process can vary widely in complexity. You\u2019re 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.<\/p>\n

\"pg<\/a><\/p>\n

<\/h3>\n

This blog is for the PG developer.<\/h3>\n

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\u2019t 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.<\/p>\n

SQL, in this article and elsewhere in Microsoft documentation, means T-SQL (Transact-SQL), Microsoft\u2019s variant of the SQL standard shared across all Microsoft SQL database engines, in the same way that PostgreSQL has its own SQL dialect. In the Microsoft SQL world, T-SQL is referred to simply as SQL.<\/div><\/p>\n

Handling date and time<\/h2>\n

Let\u2019s start with an everyday feature: handling date and time. In PostgreSQL, you have the\u00a0timestamp<\/code>\u00a0and\u00a0timestamptz<\/code>\u00a0types.<\/p>\n

PostgreSQL\u00a0timestamp<\/code><\/h3>\n

In PG, you use the\u00a0timestamp<\/code> type to store date and time values without any time zone information.<\/p>\n

PostgreSQL has had a type named timestamp<\/code> since well before PostgreSQL 7.0, but its meaning evolved. PostgreSQL 7.0 replaced the older datetime<\/code> type with timestamp<\/code>, and PostgreSQL 7.2 introduced the modern distinction between timestamp without time zone<\/code> and timestamp with time zone<\/code>. Today, a bare timestamp<\/code> means timestamp without time zone<\/code>.<\/div><\/p>\n

Using\u00a0timestamp<\/code><\/h4>\n
SELECT<\/span> TIMESTAMP<\/span> '2026-09-21 10:30:00'<\/span>;\r\n<\/code><\/pre>\n
The value of\u00a0timestamp<\/code>\u00a0in this case is stored exactly as provided. No time zone information is provided, and none is stored.<\/p>\n
SELECT<\/span> TIMESTAMP<\/span> '2026-09-21 10:30:00-06:00'<\/span>;\r\n<\/code><\/pre>\n
If you include a time zone offset,\u00a0timestamp<\/code>\u00a0simply ignores it. Even though you provided the\u00a0-06:00<\/code>\u00a0offset from UTC, that information is discarded. This behavior remains documented in PostgreSQL today.<\/p>\n
If your application needed to preserve the original time zone or offset, you had to store that information separately.<\/p>\n
CREATE<\/span> TABLE<\/span> events (\r\n    event_id SERIAL PRIMARY<\/span> KEY,\r\n    event_name TEXT NOT<\/span> NULL<\/span>,\r\n    event_timestamp TIMESTAMP<\/span> NOT<\/span> NULL<\/span>,\r\n    event_timezone TEXT NOT<\/span> NULL<\/span>\r\n);\r\n<\/code><\/pre>\n
This works, but now two values describe one event. Your application is responsible for keeping them together and interpreting them correctly.<\/p>\n
SQL\u2019s closest equivalent:\u00a0datetime2<\/code><\/h4>\n
SQL\u2019s closest equivalent to PostgreSQL\u00a0timestamp<\/code>\u00a0is\u00a0datetime2<\/code>. SQL\u2019s\u00a0datetime2<\/code>\u00a0is the modern replacement for Microsoft SQL\u2019s legacy\u00a0datetime<\/code>\u00a0type. Microsoft recommends\u00a0datetime2<\/code>\u00a0for new development.<\/p>\n
<\/i>Incredible precision<\/strong><\/p>datetime2<\/code> supports variable fractional-second precision from 0 to 7 digits and defaults to datetime2(7). At the default, it can represent time in 100 ns increments. PostgreSQL timestamp supports 6 fractional-second digits, or 1 \u03bcs increments. This gives Microsoft SQL 10\u00d7 finer representational precision over PostgreSQL.<\/div><\/p>\n
If your use case demands higher precision for scientific instruments, streaming data, or financial applications, SQL\u2019s\u00a0datetime2<\/code>\u00a0means you no longer need to compromise on precision.<\/p>\n
PostgreSQL\u00a0timestamptz<\/code><\/h3>\n
PostgreSQL 7.2 established the modern distinction between timestamp without time zone<\/code> and timestamp with time zone<\/code>, commonly written today using PostgreSQL’s timestamptz<\/code> shorthand.<\/p>\n
SELECT<\/span> TIMESTAMPTZ '2026-09-21 10:30:00-06:00'<\/span>;\r\n<\/code><\/pre>\n
This time, the offset matters. PostgreSQL uses it to determine the actual instant in time and stores that instant internally in UTC.<\/p>\n
Unfortunately, you already know the caveat<\/h4>\n
PostgreSQL does not retain the original time zone or offset. When the value is retrieved, PostgreSQL converts it to the session’s current TimeZone<\/code>.<\/p>\n
So PostgreSQL cannot answer, \u201cWhat offset did the application originally supply?\u201d And the session TimeZone<\/code> does not inherently know the user’s or application’s local time zone. The application must set the session TimeZone<\/code> appropriately or perform the conversion itself.<\/div><\/p>\n
So the same stored value can look different depending on who asks for it:<\/p>\n
SET<\/span> TIME<\/span> ZONE 'America\/Denver'<\/span>;\r\n\r\nSELECT<\/span> TIMESTAMPTZ '2026-09-21 10:30:00-06:00'<\/span>;\r\n-- 2026-09-21 10:30:00-06<\/span>\r\n\r\nSET<\/span> TIME<\/span> ZONE 'America\/New_York'<\/span>;\r\n\r\nSELECT<\/span> TIMESTAMPTZ '2026-09-21 10:30:00-06:00'<\/span>;\r\n-- 2026-09-21 12:30:00-04<\/span><\/code><\/pre>\n
Both results represent exactly the same instant. That\u2019s intentional PostgreSQL behavior. But if your application needs to know that the original value was\u00a010:30 -06:00<\/code>,\u00a0timestamptz<\/code>\u00a0cannot tell you. That information is lost during the insert.<\/p>\n
It can be frustrating.<\/strong> 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\u00a0timestamptz<\/code>\u00a0is that PostgreSQL normalizes timestamps to a common instant and automatically converts them to the session\u2019s configured time zone when retrieved.<\/p>\n
Good news! You’re using SQL now.<\/h2>\n
Introducing SQL\u2019s\u00a0datetimeoffset<\/code>\u00a0type. When time zone information is important,\u00a0datetimeoffset<\/code>\u00a0allows you to store both the local date and time as well as the offset from UTC, preserving the original context of the timestamp.<\/p>\n
DECLARE<\/span> @dt<\/span> DATETIMEOFFSET =<\/span> '2026-09-21 10:30:00-06:00'<\/span>;<\/code><\/pre>\n
This advanced type has been around since SQL Server 2008. Ubiquitous in modern Microsoft SQL applications, it provides a flexible way to handle time zone information.<\/p>\n
Here\u2019s how it works<\/h3>\n
The\u00a0datetimeoffset<\/code>\u00a0type stores both the local date and time as well as the offset from UTC. This means that when you insert a value like\u00a0'2026-09-21 10:30:00-06:00'<\/code>, Microsoft SQL retains the\u00a0-06:00<\/code>\u00a0offset along with the date and time, preserving the original context of the timestamp.<\/p>\n
Do you want to show it as UTC? You have two easy options:<\/p>\n
SELECT<\/span> @dt<\/span> AT<\/span> TIME<\/span> ZONE 'UTC'<\/span>;\r\n\r\nSELECT<\/span> SWITCHOFFSET(@dt<\/span>, '+00:00'<\/span>);<\/code><\/pre>\n
<\/i>Both return the same instant at UTC.<\/strong><\/p>Both return the same instant at UTC.\u00a0AT TIME ZONE<\/code>\u00a0is useful when working with named time zones and their conversion rules.\u00a0SWITCHOFFSET<\/code>\u00a0is simpler when you already have a\u00a0datetimeoffset<\/code>\u00a0and just want to change its displayed offset to UTC.<\/div><\/p>\n
By the way, that same ultra-high precision you enjoy with SQL\u2019s\u00a0datetime2<\/code>\u00a0is available with\u00a0datetimeoffset<\/code>, but with the added benefit of time zone awareness.<\/p>\n
-- Example showing high precision with datetimeoffset<\/span>\r\n\r\nDECLARE<\/span> @dtHighPrecision<\/span> DATETIMEOFFSET(7<\/span>) =<\/span>\r\n    '2026-09-21 10:30:00.1234567-06:00'<\/span>;\r\n\r\nSELECT<\/span> @dtHighPrecision<\/span>;\r\n<\/code><\/pre>\n
Storage differences<\/h3>\n
datetimeoffset<\/code>\u00a0requires 8 to 10 bytes of storage, depending on fractional-second precision. This is slightly more than\u00a0datetime2<\/code>, which requires 6 to 8 bytes. The additional 2 bytes store the time zone offset.<\/p>\n
Consider the impact of this additional storage across a billion rows. Attending to storage doesn\u2019t just save disk space; it can also reduce I\/O, memory pressure, and index size.<\/p>\n\n\n\n\n\n
Rows<\/th>\ndatetimeoffset<\/code><\/th>\ndatetime2<\/code><\/th>\n<\/tr>\n<\/thead>\n
1 B<\/td>\n8\u201310 GB<\/td>\n6\u20138 GB<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
That\u2019s 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.<\/p>\n
Your default type for dates<\/h4>\n
Because of this storage difference, your default should not always be\u00a0datetimeoffset<\/code>, especially when your app doesn\u2019t care about the time zone. In many cases,\u00a0datetime2<\/code>\u00a0remains an excellent choice.<\/p>\n
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.<\/p>\n\n\n\n\n\n
Rows<\/th>\ndatetimeoffset(0)<\/code><\/th>\ndatetimeoffset(7)<\/code><\/th>\n<\/tr>\n<\/thead>\n
1 B<\/td>\n8 GB<\/td>\n10 GB<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
That\u2019s a 2 GB difference in storage for a billion rows when choosing between the lowest and highest precision for\u00a0datetimeoffset<\/code>. What\u2019s more, you still have time zone support when you need it.<\/p>\n
Congratulations<\/h2>\n
Let me welcome you again to SQL!<\/strong> I hope it\u2019s clear that you\u2019ve 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.<\/p>\n
Happy querying!<\/p>\n
Learn more about Azure SQL database<\/a><\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
Moving from PostgreSQL to SQL Server? Learn the key differences between timestamp, timestamptz, datetime2, and datetimeoffset, including precision, time zones, offsets, and storage.<\/p>\n","protected":false},"author":96788,"featured_media":7617,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,672,619],"tags":[733,751,750],"class_list":["post-7603","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-sql-server-2025","category-t-sql","tag-microsoft-sql","tag-modernize","tag-postgresql"],"acf":[],"blog_post_summary":"
Moving from PostgreSQL to SQL Server? Learn the key differences between timestamp, timestamptz, datetime2, and datetimeoffset, including precision, time zones, offsets, and storage.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7603","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/96788"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=7603"}],"version-history":[{"count":2,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7603\/revisions"}],"predecessor-version":[{"id":7631,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7603\/revisions\/7631"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/7617"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=7603"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=7603"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=7603"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}