Differences

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

--- sql-server-to-postgresql:getutcdate [August 10, 2026 8:03 am]
sqlines created
+++ sql-server-to-postgresql:getutcdate [August 10, 2026 10:35 am] (current)
@@ Line 1: / Line 1: @@
-GETDATE Function - SQL Server to PostgreSQL Migration
+====== GETUTCDATE Function - SQL Server to PostgreSQL Migration ======
+In SQL Server, the GETUTCDATE function returns the current date and time with **milliseconds** precision in the UTC time zone.
+GETUTCDATE() 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() AT TIME ZONE 'UTC' function, which also returns the **actual** current date and time in the UTC time zone.
+**SQL Server**:
+<code sql>
+  -- Get the current date and time with milliseconds precision in UTC
+  SELECT GETUTCDATE();
+  /* 2026-08-10 08:10:14.627 */
+</code>
+**PostgreSQL**:
+<code sql>
+  -- Get the current date and time with milliseconds precision in UTC
+  SELECT (CLOCK_TIMESTAMP() AT TIME ZONE 'UTC')::TIMESTAMP(3);
+  /* 2026-08-10 08:10:14.627 */
+  -- Without a cast to TIMESTAMP(3), CLOCK_TIMESTAMP uses microseconds precision
+  SELECT CLOCK_TIMESTAMP() AT TIME ZONE 'UTC';
+  /* 2026-08-10 08:10:14.627403 */
+</code>
+===== Execution Time - Why not NOW() or CURRENT_TIMESTAMP in PostgreSQL =====
+Unlike the GETDATE() 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 GETUTCDATE();
+  /* 2026-08-10 08:15:06.140 */
+  -- Wait 3 seconds
+  WAITFOR DELAY '00:00:03'
+  -- Get the current date and time again (reflects the 3-second wait)
+  SELECT GETUTCDATE();
+  /* 2026-08-10 08:15:09.140 */
+  COMMIT;
+</code>
+**PostgreSQL**:
+<code sql>
+  -- Start a new transaction
+  BEGIN;
+  -- Get the current date and time
+  SELECT CLOCK_TIMESTAMP() AT TIME ZONE 'UTC', NOW() AT TIME ZONE 'UTC',
+    CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
+  /* 2026-08-10 08:15:06.140739 | 2026-08-10 08:15:06.169246 | 2026-08-10 08:15:06.169246 */
+  -- 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() AT TIME ZONE 'UTC', NOW() AT TIME ZONE 'UTC',
+    CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
+  /* 2026-08-10 08:15:09.140742 | 2026-08-10 08:15:06.169246 | 2026-08-10 08:15:06.169246 */
+  COMMIT;
+</code>
+You can see that within the same transaction, NOW() and CURRENT_TIMESTAMP always return the same value regardless of the transaction's duration. This might not always be the desired behavior. {{:exclamation.png|}}
+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