Differences

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

--- sql-server-to-postgresql:sysdatetime [August 10, 2026 8:43 am]
sqlines created
+++ sql-server-to-postgresql:sysdatetime [August 10, 2026 10:16 am] (current)
@@ Line 1: / Line 1: @@
-====== GETUTCDATE Function - SQL Server to PostgreSQL Migration ======
+====== SYSDATETIME Function - SQL Server to PostgreSQL Migration ======
+In SQL Server, the SYSDATETIME function returns the current date and time with 100 nanoseconds precision (7 fractional digits) **without** the time zone for the database server's host **operating system**.
+SYSDATETIME() 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 date and time with 100 nanoseconds precision
+  SELECT SYSDATETIME();
+  /* 2026-08-10 12:01:50.1839299 */
+</code>
+**PostgreSQL**:
+<code sql>
+  -- Get the current date and time with microseconds precision
+  SELECT CLOCK_TIMESTAMP()::TIMESTAMP;
+  /* 2026-08-10 12:01:50.183929 */
+  -- Without a cast to TIMESTAMP, CLOCK_TIMESTAMP includes the time zone
+  SELECT CLOCK_TIMESTAMP();
+  /* 2026-08-10 12:01:50.183929-07 */
+</code>
+===== Execution Time - Why not NOW() or LOCALTIMESTAMP in PostgreSQL =====
+Unlike the SYSDATETIME() and CLOCK_TIMESTAMP() functions, which return the **actual** current date and time, the NOW() and LOCALTIMESTAMP 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 SYSDATETIME();
+  /* 2026-08-10 12:08:33.8658770 */
+  -- Wait 3 seconds
+  WAITFOR DELAY '00:00:03'
+  -- Get the current date and time again (reflects the 3-second wait)
+  SELECT SYSDATETIME();
+  /* 2026-08-10 12:08:36.8682106 */
+  COMMIT;
+</code>
+**PostgreSQL**:
+<code sql>
+  -- Start a new transaction
+  BEGIN;
+  -- Get the current date and time
+  SELECT CLOCK_TIMESTAMP(), NOW(), LOCALTIMESTAMP;
+  /* 2026-08-10 12:08:33.865877-07 | 2026-08-10 12:08:33.865317-07 | 2026-08-10 12:08:33.865317 */
+  -- 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(), LOCALTIMESTAMP;
+  /* 2026-08-10 12:08:36.866251-07 | 2026-08-10 12:08:33.865317-07 | 2026-08-10 12:08:33.865317 */
+  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(), LOCALTIMESTAMP;
+  /* 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(), LOCALTIMESTAMP;
+  /* 2026-08-10 11:39:07.075324-04 | 2026-08-10 11:39:07.075248-04 | 2026-08-10 11:39:07.075248 */
+</code>
+For more information, see [[/sql-server-to-postgresql|SQL Server to PostgreSQL Migration]].
+~~NOTOC~~

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