SYSDATETIMEOFFSET Function - SQL Server to PostgreSQL Migration

In SQL Server, the SYSDATETIMEOFFSET function returns the current date and time with 100 nanoseconds precision (7 fractional digits) with the time zone for the database server's host operating system.

SYSDATETIMEOFFSET() returns the actual current date and time, but SQL Server can optimize how many times it is evaluated within a statement (run-time constant).

In PostgreSQL you can use the CLOCK_TIMESTAMP function, which also returns the actual current date and time with microseconds precision (6 fractional digits) and in the client's session time zone.

SQL Server:

  -- Get the current date and time with 100 nanoseconds precision
  SELECT SYSDATETIMEOFFSET();
  /* 2026-08-10 12:01:50.1839299 -07:00*/

PostgreSQL:

  -- Get the current date and time with microseconds precision
  SELECT CLOCK_TIMESTAMP()::TIMESTAMP;
  /* 2026-08-10 12:01:50.183929-07 */

Unlike the SYSDATETIMEOFFSET() and CLOCK_TIMESTAMP() functions, which return the actual current date and time, the NOW() and CURRENT_TIMESTAMP functions return the start date and time of the current transaction.

SQL Server:

  -- Start a new transaction
  BEGIN TRANSACTION
 
  -- Get the current date and time
  SELECT SYSDATETIMEOFFSET();
  /* 2026-08-10 12:08:33.8658770 -07:00 */
 
  -- Wait 3 seconds
  WAITFOR DELAY '00:00:03'
 
  -- Get the current date and time again (reflects the 3-second wait)
  SELECT SYSDATETIMEOFFSET();
  /* 2026-08-10 12:08:36.8682106 -07:00 */ 
 
  COMMIT;

PostgreSQL:

  -- Start a new transaction
  BEGIN;
 
  -- Get the current date and time
  SELECT CLOCK_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP;
  /* 2026-08-10 12:08:33.865877-07 | 2026-08-10 12:08:33.865317-07 | 2026-08-10 12:08:33.865317-07 */
 
  -- Wait 3 seconds
  SELECT PG_SLEEP(3);
 
  -- Get the current date and time again (Only CLOCK_TIMESTAMP reflects the 3-second wait)
  SELECT CLOCK_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP;
  /* 2026-08-10 12:08:36.866251-07 | 2026-08-10 12:08:33.865317-07 | 2026-08-10 12:08:33.865317-07 */ 
 
  COMMIT;

You can see that within the same transaction, NOW() and LOCALTIMESTAMP always return the same value regardless of the transaction's duration. This might not always be the desired behavior.

In SQL Server, SYSDATETIME() always uses the server instance time zone, while PostgreSQL functions use the client's session time zone.

PostgreSQL:

  -- Get the current time zone
  SHOW TIMEZONE;
  /* America/Los_Angeles */
 
  -- Get the current date and time
  SELECT CLOCK_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP;
  /* 2026-08-10 08:37:56.774409-07 | 2026-08-10 08:37:56.774334-07 | 2026-08-10 08:37:56.774334 */
 
  -- Change the client's time zone
  SET SESSION TIME ZONE 'America/New_York';
 
  -- Now functions return date and time in the new time zone
  SELECT CLOCK_TIMESTAMP(), NOW(), CURRENT_TIMESTAMP;
  /* 2026-08-10 11:39:07.075324-04 | 2026-08-10 11:39:07.075248-04 | 2026-08-10 11:39:07.075248 */

For more information, see SQL Server to PostgreSQL Migration.