Differences

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

--- sql-server-to-postgresql:current_timestamp [August 10, 2026 6:52 am]
sqlines
+++ sql-server-to-postgresql:current_timestamp [August 10, 2026 7:24 am] (current)
@@ Line 1: / Line 1: @@
 ====== CURRENT_TIMESTAMP Function - SQL Server to PostgreSQL Migration ======
-In SQL Server, the CURRENT_TIMESTAMP function returns the current date and time with milliseconds precision without time zone (it uses the server time zone).
+In SQL Server, the CURRENT_TIMESTAMP function returns the current date and time with **milliseconds** precision **without** the time zone for the database server's host **operating system**.
-In PostgreSQL, you can also use the CURRENT_TIMESTAMP function but it returns the current date and time with microseconds precision and includes the time zone.
+CURRENT_TIMESTAMP 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, or the CURRENT_TIMESTAMP(3) function, which returns the **start** of the current **transaction**. {{:exclamation.png|}}
+Note that PostgreSQL functions use the **client's session** time zone. {{:exclamation.png|}}
 **SQL Server**:
 <code sql>
-  -- Get the current date and time
+  -- Get the current date and time with milliseconds precision
   SELECT CURRENT_TIMESTAMP;
-  # 2024-10-22 21:55:27.067
+  /* 2026-08-08 17:43:36.913 */
 </code>
 **PostgreSQL**:
 <code sql>
-  -- Get the current date and time
+  -- Get the current date and time with milliseconds precision
+  SELECT CLOCK_TIMESTAMP()::TIMESTAMP(3);
+  /* 2026-08-08 17:43:36.913 */
+  -- Without a cast to TIMESTAMP(3), CLOCK_TIMESTAMP uses microseconds precision and includes the time zone
+  SELECT CLOCK_TIMESTAMP();
+  /* 2026-08-08 17:43:36.913794-07 */
+  -- CURRENT_TIMESTAMP uses microseconds precision by default and includes the time zone
+  SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP(3);
+  /* 2026-08-08 17:44:37.850512-07 | 2026-08-08 17:44:37.851-07 */
+</code>
+===== Execution Time - Why not CURRENT_TIMESTAMP in PostgreSQL =====
+Unlike SQL Server, where CURRENT_TIMESTAMP returns the **actual** current date and time, in PostgreSQL it returns 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 CURRENT_TIMESTAMP;
-  # 2024-10-22 21:55:27.067329+02
+  /* 2026-08-08 18:08:26.030 */
+  -- Wait 3 seconds
+  WAITFOR DELAY '00:00:03'
+  -- Get the current date and time again (reflects the 3-second wait)
+  SELECT CURRENT_TIMESTAMP;
+  /* 2026-08-08 18:08:29.030 */
+  COMMIT;
+</code>
+**PostgreSQL**:
+<code sql>
+  -- Start a new transaction
+  BEGIN;
+  -- Get the current date and time
+  SELECT CLOCK_TIMESTAMP(), CURRENT_TIMESTAMP;
+  /* 2026-08-08 08:21:42.82698-07 | 2026-08-08 08:21:42.823941-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(), CURRENT_TIMESTAMP;
+  /* 2026-08-08 08:21:45.886739-07 | 2026-08-08 08:21:42.823941-07 */
+  COMMIT;
+</code>
+You can see that within the same transaction, PostgreSQL CURRENT_TIMESTAMP always returns 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, CURRENT_TIMESTAMP 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(), CURRENT_TIMESTAMP;
+  /* 2026-08-08 08:37:56.774409-07 | 2026-08-08 08:37:56.774334-07 */
+  -- 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(), CURRENT_TIMESTAMP;
+  /* 2026-08-08 11:39:07.075324-04 | 2026-08-08 11:39:07.075248-04 */
 </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