Differences

This shows you the differences between two versions of the page.

--- sql-server-to-postgresql:sysdatetimeoffset [August 10, 2026 10:08 am]
sqlines
+++ sql-server-to-postgresql:sysdatetimeoffset [August 10, 2026 10:14 am] (current)
@@ Line 1: / Line 1: @@
 ====== SYSDATETIMEOFFSET Function - SQL Server to PostgreSQL Migration ======
-In SQL Server, the SYSDATETIMEOFFSET function returns the current date and time with 100 nanoseconds precision with the time zone of the current database instance.
+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**.
-In PostgreSQL you can use the NOW function that also includes time zone.
+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). {{:exclamation.png|}}
+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. {{:exclamation.png|}}
 **SQL Server**:
 <code sql>
-  -- Get the current datetime with time zone
+  -- Get the current date and time with 100 nanoseconds precision
   SELECT SYSDATETIMEOFFSET();
-  /* 2024-12-11 12:38:31.3759175 +01:00 */
+  /* 2026-08-10 12:01:50.1839299 -07:00*/
 </code>
@@ Line 16: / Line 18: @@
 <code sql>
-  -- Get the current datetime with time zone
+  -- Get the current date and time with microseconds precision
-  SELECT NOW();
+  SELECT CLOCK_TIMESTAMP()::TIMESTAMP;
-  /* 2024-12-11 12:38:31.375917+01 */
+  /* 2026-08-10 12:01:50.183929-07 */
+</code>
+===== Execution Time - Why not NOW() or CURRENT_TIMESTAMP in PostgreSQL =====
+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**. {{:exclamation.png|}}
+**SQL Server**:
+<code sql>
+  -- 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;
+</code>
+**PostgreSQL**:
+<code sql>
+  -- 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;
+</code>
+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. {{:exclamation.png|}}
+===== Database Server and Session Time Zone =====
+In SQL Server, SYSDATETIME() always uses the **server** instance time zone, while PostgreSQL functions use the **client's session** time zone. {{:exclamation.png|}}
+**PostgreSQL**:
+<code sql>
+  -- 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 */
 </code>

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications

Differences