GETDATE Function - SQL Server to PostgreSQL Migration

In SQL Server, the GETDATE function returns the current date and time with milliseconds precision without the time zone for the database server's host operating system.

GETDATE() 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 but in the client's session time zone.

SQL Server:

  -- Get the current date and time with milliseconds precision
  SELECT GETDATE();
  /* 2026-08-08 17:43:36.913 */

PostgreSQL:

  -- 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 */

Unlike the GETDATE() 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.

SQL Server:

  -- Start a new transaction
  BEGIN TRANSACTION
 
  -- Get the current date and time
  SELECT GETDATE();
  /* 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 GETDATE();
  /* 2026-08-08 18:08:29.030 */ 
 
  COMMIT;

PostgreSQL:

  -- Start a new transaction
  BEGIN;
 
  -- Get the current date and time
  SELECT CLOCK_TIMESTAMP(), NOW(), LOCALTIMESTAMP;
  /* 2026-08-08 08:21:42.82698-07 | 2026-08-08 08:21:42.823941-07 | 2026-08-08 08:21:42.823941 */
 
  -- 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-08 08:21:45.886739-07 | 2026-08-08 08:21:42.823941-07 | 2026-08-08 08:21:42.823941 */ 
 
  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, GETDATE() 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(), LOCALTIMESTAMP;
  /* 2026-08-08 08:37:56.774409-07 | 2026-08-08 08:37:56.774334-07 | 2026-08-08 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-08 11:39:07.075324-04 | 2026-08-08 11:39:07.075248-04 | 2026-08-08 11:39:07.075248 */

For more information, see SQL Server to PostgreSQL Migration.