PostgreSQL to SQL Server: Preserve Timestamp Meaning

For SQL Server and Azure SQL teams, the decision starts before schema conversion. PostgreSQL’s timestamp and timestamptz have different contracts, while SQL Server’s datetime2 and datetimeoffset offer related but not identical choices. Moving between them requires an explicit decision about which facts the application needs to retain.

Microsoft emphasizes finer fractional-second precision and built-in offset preservation. Those are real capabilities, but neither automatically repairs information already discarded by a source database. A sound migration preserves existing meaning first, then deliberately changes the data contract for future writes where that is useful.

PostgreSQL-to-SQL Server migration begins with the meaning of timestamp​

The word “SQL” in Microsoft’s “PostgreSQL to SQL Field Notes: Date & Time” refers to Microsoft’s SQL database technologies. PostgreSQL already uses SQL, and its documentation explicitly identifies bare timestamp as the SQL-standard shorthand for timestamp without time zone. The comparison is between database engines and their types, not between PostgreSQL and the SQL language.

PostgreSQL’s two timestamp variants both occupy eight bytes and have a documented resolution of one microsecond. Their principal difference is how they interpret and present time-zone information, rather than storage size or fractional-second capacity. PostgreSQL’s current date/time reference documents those common physical characteristics.

Consider this PostgreSQL expression from Microsoft’s field notes:

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

The explicit TIMESTAMP type determines how PostgreSQL interprets the literal. PostgreSQL’s documentation says that, once a value is treated as timestamp without time zone, any time-zone indication is silently ignored. The result retains September 21 at 10:30, without applying or retaining the -06:00 offset.

That behavior is appropriate when the application intentionally needs a wall-clock value: a calendar date and clock time without a specified relationship to UTC. It is inappropriate if the application expected the offset to establish when an event occurred globally. The same stored value can therefore reflect either a deliberate design or an ingestion mistake; inspecting the column declaration alone cannot distinguish them.

SQL Server’s closest counterpart for that zone-less value is datetime2. Microsoft Learn documents that datetime2 has no time-zone offset awareness or preservation, and Microsoft recommends the modern date/time types rather than legacy datetime for new work. Choosing datetime2 preserves the zone-less nature of a PostgreSQL timestamp; it does not make that value UTC.

A useful starting map looks like this:

The second row intentionally does not prescribe a mechanical replacement. An application can choose an offset-bearing representation for instants, or a documented UTC convention with datetime2. The latter is an application contract rather than a property enforced by the type. Whichever design is selected, conversion must preserve the instant instead of merely copying the displayed clock fields.

PostgreSQL timestamptz preserves the instant, not the original presentation​

PostgreSQL’s timestamptz is shorthand for timestamp with time zone. Its name can suggest that the original time zone travels with the value, but PostgreSQL’s documentation describes a different model: use the input zone to determine the instant, store that instant internally as UTC, and convert it to the session’s current TimeZone for output.

The field notes illustrate the distinction with an explicitly offset-bearing value:

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

Here, unlike the TIMESTAMP example, the offset participates in interpretation. By straightforward offset arithmetic, 10:30 at UTC minus six hours represents 16:30 UTC on the same date. The local clock reading and UTC representation describe one instant.

Changing the PostgreSQL session’s TimeZone can change the displayed date/time and offset without changing that instant. Microsoft demonstrates this by selecting the same value after setting the session to America/Denver and then America/New_York. PostgreSQL’s documentation confirms that this output conversion is intentional.

Session formatting must not become invented historical context​

This has a direct consequence for migration exports. If a process reads a PostgreSQL timestamptz as text and sees an offset, that offset reflects the output time zone. It does not prove that the client supplied that offset when the record was created.

Moving the exported value into datetimeoffset can preserve the instant and the exported representation. It cannot transform the export offset into evidence of the original client offset. Treating the two as equivalent would add a historical claim the source column cannot support.

For example, the field-notes value can be represented as 16:30 at +00:00. Storing that representation in SQL Server is a legitimate way to preserve the instant. Describing +00:00 as the offset originally submitted by the user would be incorrect unless some other retained record establishes it.

This is the migration boundary worth making explicit in schema and application reviews: a richer target type cannot recover information the source did not retain. Original offsets may still exist in separate columns or other retained input records, but they cannot be derived from a timestamptz value alone.

Missing input offsets make the PostgreSQL session part of ingestion​

PostgreSQL’s documented behavior also matters when input contains no explicit zone. For timestamp with time zone, PostgreSQL assumes the input belongs to the configured TimeZone and converts accordingly. That means the input session configuration can affect the stored instant, not merely its later display.

A migration review should therefore distinguish explicit-offset inputs from zone-less inputs that relied on session settings. Both may end up in the same timestamptz column, yet their interpretation depended on different evidence at ingestion. Copying the column declaration into a mapping spreadsheet will not capture that distinction.

The practical acceptance criterion is preservation of the instant across representations. Different strings can be correct when they encode the same instant; matching clock fields can be wrong when their offsets or assumed zones differ. This is why comparing formatted output alone is an incomplete validation strategy.

SQL Server datetimeoffset retains an offset, while named zones remain separate​

Microsoft’s field notes identify datetimeoffset as the built-in choice when an application needs to retain the local date/time and its numeric UTC offset together. The post dates the type to SQL Server 2008. Its appearance in the September guidance is therefore a migration lesson, not a newly introduced feature.

The central example is simple:

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

SELECT @dt;

Unlike PostgreSQL timestamptz, the SQL Server value retains the supplied -06:00 offset alongside the date/time. For an application whose input contract includes that offset, this removes the need for a separate offset field merely to preserve those components.

The scope of that preservation needs careful wording. A numeric offset states the difference from UTC for the represented value. A named zone, such as America/Denver, identifies a region whose rules can determine different offsets on different dates. PostgreSQL’s time-zone documentation explains this distinction and notes that named-zone rules can change through political decisions.

Consequently, storing -06:00 does not establish which region the user selected. It also does not supply the regional rule needed to interpret some other date. If the business requirement includes the original named zone, retain that identifier separately even when the timestamp itself uses datetimeoffset.

This limits Microsoft’s description of the type as preserving the timestamp’s “original context.” It preserves the supplied local date/time and numeric offset. That is useful context, but it is not every possible piece of time-zone context.

SWITCHOFFSET changes the representation without moving the instant​

Microsoft supplies two ways to express its offset-bearing example at UTC:

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

SELECT @dt AT TIME ZONE 'UTC';
SELECT SWITCHOFFSET(@dt, '+00:00');

For this input, the documented purpose of both expressions is to return the same instant at UTC. The expected clock component is 16:30 on September 21, with an offset of +00:00; that follows from the six-hour difference in the input, not from a WindowsForum test.

The field notes distinguish their intended uses. SWITCHOFFSET suits an existing datetimeoffset when the desired numeric offset is already known. AT TIME ZONE is useful when named-zone conversion rules are involved. These examples establish a UTC conversion for an already offset-bearing value; they do not establish a universal recipe for interpreting zone-less local timestamps.

That boundary matters during translation. A value that already identifies an instant needs a change of representation. A zone-less value needs an interpretation before it can identify an instant. Migration code should make that difference visible instead of treating every time-zone operation as formatting.

Casting to datetime2 does not normalize a value to UTC​

Microsoft Learn documents another consequential behavior: converting a datetimeoffset to datetime2 copies the date/time components and removes the offset. It does not first adjust the clock to UTC.

Applied to the field-notes example, directly removing the offset leaves 10:30 without zone context. The UTC clock reading for the same instant is 16:30. If an application then labels the resulting 10:30 value as UTC, it has changed the meaning by six hours.

A target design that stores UTC in datetime2 therefore needs an explicit normalization step before the offset is removed. The supported distinction is the order of operations: first express the instant at UTC, then retain those UTC clock fields under the application’s UTC convention. Dropping the offset first destroys the information needed to perform that conversion correctly.

This is a particularly useful addition to a type-mapping review because both outcomes can look syntactically valid. The database accepting a value does not establish that the application applied the intended interpretation.

datetime2 precision and storage require separate decisions​

SQL Server’s datetime2 supports zero through seven fractional-second digits, with seven as the default. Microsoft Learn documents 100-nanosecond representational granularity at that maximum precision. PostgreSQL’s timestamp types support up to six fractional digits and have a documented one-microsecond resolution.

Microsoft’s claim of ten times finer representational precision follows from those figures. One microsecond is 1,000 nanoseconds; a 100-nanosecond increment is one tenth of that. The comparison concerns what the type can represent.

It does not establish that the source clock or application measured events with that accuracy. Nor does migration into a seven-digit type create a previously unrecorded seventh digit of information. For existing PostgreSQL timestamp data, six fractional digits can preserve the source type’s documented fractional resolution, subject to the rest of the conversion being correct.

The same distinction applies to Microsoft’s examples involving scientific, streaming, or financial data. A finer target representation can be useful when new inputs actually contain finer values. The type specification alone does not prove that a particular workload gains more accurate event timing.

Storage changes at precision bands, not at every digit​

Microsoft Learn’s datetime2 documentation gives these storage sizes for uncompressed rowstore. Microsoft’s field notes state that datetimeoffset requires two additional bytes to retain the offset, producing the following comparison at equivalent precision:

A useful consequence is that choosing precision six instead of seven does not reduce the documented value size in this storage model. Both sit in the same band. Choosing six may accurately express the source-data contract, but it should not be presented as a per-value storage saving over seven.

Moving to a lower band can reduce value storage, but it also reduces the fractional-second information the type can represent. That is a semantic trade-off, not a free optimization. If microseconds matter to the application, choosing millisecond precision to save space changes the data contract.

The field notes illustrate the scale with one billion values. A two-byte difference multiplied by one billion is two billion bytes, or 2 GB in decimal units. That arithmetic applies both to equivalent-precision datetime2 versus datetimeoffset values and to the illustrated difference between datetimeoffset(0) and datetimeoffset(7).

Those totals are value-storage estimates, not complete database-size or performance predictions. Microsoft Learn expressly qualifies its datetime2 figures as uncompressed rowstore sizes and notes that compression, columnstore, and in-memory execution can behave differently. Table and index structure also have to be considered before turning a per-value difference into a capacity plan.

SQL Server’s greater fractional precision does not mean a larger date range​

There is another migration boundary that the precision comparison can obscure. PostgreSQL documents timestamps extending from 4713 BC to 294276 AD. Microsoft Learn documents datetime2 from January 1, year 1, through December 31, 9999.

For a source column whose values all fall within the target range, that difference presents no range-conversion issue. But a schema-level mapping from PostgreSQL timestamp to SQL Server datetime2 is not a guarantee that every value the source type can hold is representable in the destination.

PostgreSQL also documents special timestamp values infinity and -infinity. Such values require an explicit application-level migration policy rather than an assumption that a normal finite timestamp conversion will preserve their meaning. Silently replacing an unbounded value with an arbitrary ordinary date would change the contract.

The same care is needed when applying SQL Server advice across Microsoft products. Microsoft Learn’s datetime2 page specifically limits Microsoft Fabric Data Warehouse to precision zero through six, with precision required rather than defaulted. The SQL Server default of seven should not be generalized to every Microsoft service that exposes a type named datetime2.

Preserve PostgreSQL timestamp semantics before changing production writes​

Choose the target representation only after documenting whether each source column carries wall-clock fields, an instant, an original offset, or a named zone. This is the decision that determines whether a conversion is correct; storage tuning and extra fractional digits come afterward.

The following review sequence follows from the documented behaviors. It is a migration validation framework, not an engine-specific deployment or rollback script.

1. Classify each source column’s meaning. Distinguish timestamp without time zone from timestamptz, then establish what the application intended. For a zone-less column, record whether the values are genuinely local clock readings or follow an application convention such as UTC.
2. Trace how incoming values were interpreted. Identify inputs with explicit offsets and inputs that relied on PostgreSQL’s session TimeZone. Where a timestamp input included an offset, account for PostgreSQL’s documented behavior of ignoring it rather than assuming it survived.
3. Identify separately retained context. Establish whether original offsets or named-zone identifiers exist outside the timestamp column. Keep that evidence distinct from the offset produced by a later export session.
4. Select the target type and precision deliberately. Use datetime2 for values whose contract excludes an offset, and consider datetimeoffset when retaining the numeric offset is part of the requirement. For an instant-only design using UTC datetime2, specify normalization explicitly.
5. Define success in terms of retained information. A wall-clock migration should retain the intended date/time fields and precision. An instant migration should retain the instant across representations. An offset-preserving migration should retain the supplied offset where that information actually exists.
6. Review values outside the ordinary path. Include source dates outside the target range, PostgreSQL infinity values, and any precision reduction. Where named zones are involved, include dates affected by the relevant daylight-saving rules rather than validating only one ordinary timestamp.

The ordered review deliberately separates existing records from future writes. Old PostgreSQL timestamptz records may have no recoverable original offset, while new SQL Server datetimeoffset records can retain one at ingestion. Both can coexist under a documented policy, but applications must not pretend that the older rows have evidence they never contained.

Client handling belongs in the same review. Microsoft Learn notes that some older clients do not support the modern date/time types directly and can receive string representations instead. A correct target column definition is therefore only part of the path: validation needs to include the application’s write and read behavior, particularly whether fractional seconds and offsets survive that path.

No production cutover or recovery mechanism is established by this type comparison. Its practical purpose is to determine what a successful migration must preserve before a team applies its deployment process. Where interpretation remains unresolved, changing the type alone is not a safe substitute for resolving it.

The concrete takeaways are:

• Map PostgreSQL timestamp to SQL Server datetime2 only after confirming the intended meaning of its zone-less clock fields.
• Treat PostgreSQL timestamptz as an instant whose output depends on the session, not as a record of the client’s original offset.
• Use datetimeoffset to retain a supplied numeric offset, and retain a named-zone identifier separately when regional context is required.
• Normalize an offset-bearing value to UTC before removing its offset if the destination contract is UTC datetime2.
• Choose fractional precision and storage with the documented bands in mind, while checking date-range and special-value compatibility separately.

Microsoft’s field notes provide a useful entry point because the types are familiar enough to invite an overly simple translation. The reliable migration is the one that makes the information contract explicit: which clock fields must remain unchanged, which instant must survive conversion, and which offset or zone was genuinely recorded. Once those decisions are settled, SQL Server’s type choices become straightforward—and future writes can preserve additional context without inventing it for the past.