DATETIMEOFFSET Data Type - SQL Server to PostgreSQL Migration

In SQL Server, the DATETIMEOFFSET(p) data type stores date and time with fractional seconds and time zone. In PostgreSQL, you can use the TIMESTAMP(p) WITH TIME ZONE data type.

SQL Server:

  -- Sample table with DATETIMEOFFSET with milliseconds accuracy
  CREATE TABLE specs
  (
    item VARCHAR(30),
    created_dt DATETIMEOFFSET(3)
  );
 
  -- Insert a sample row
  INSERT INTO specs VALUES ('A', '2024-11-15T13:07:35.123+01:00');

PostgreSQL:

  -- Sample table with TIMESTAMP WITH TIME ZONE with milliseconds accuracy
  CREATE TABLE specs
  (
    item VARCHAR(30),
    created_dt TIMESTAMP(3) WITH TIME ZONE
  );
 
  -- Insert a sample row
  INSERT INTO specs VALUES ('A', '2024-11-15T13:07:35.123+01:00');

Conversion summary:

	SQL Server	PostgreSQL
Syntax	DATETIMEOFFSET[(p)]	TIMESTAMP[(p)] WITH TIME ZONE
Range	0 ⇐ p ⇐ 7	0 ⇐ p ⇐ 6
Date range	0001-01-01 to 9999-12-31	4713 BC to 294276 AD
Accuracy	100 nanoseconds	1 microsecond
Default	p is 7	p is 6

For more information, see SQL Server to PostgreSQL Migration.