CAST(TIMEOFDAY() AS TIMESTAMP) - PostgreSQL to Oracle Migration

In PostgreSQL, CURRENT_TIMESTAMP and NOW() functions return the date and time when the transaction was started, not the current date and time.

PostgreSQL:

  -- Start a transaction
  BEGIN;
 
  SELECT CURRENT_TIMESTAMP;
  # 2013-03-09 10:25:21.168+00
 
  -- Wait 3 seconds
  SELECT pg_sleep(3);
 
  -- The same time is returned
  SELECT CURRENT_TIMESTAMP;
  # 2013-03-09 10:25:21.168+00

At the same time, TIMEOFDAY() functon returns the current date and time but the result is a string, so CAST(TIMEOFDAY() AS TIMESTAMP) expression is often used to return the current date and time as a timestamp value in PostgreSQL.

  -- Start a transaction
  BEGIN;
 
  SELECT CAST(TIMEOFDAY() AS TIMESTAMP);
  # 2013-03-09 10:36:23.79
 
  -- Wait 3 seconds
  SELECT pg_sleep(3);
 
  -- The current date and time is returned (3 seconds elapsed)
  SELECT CAST(TIMEOFDAY() AS TIMESTAMP);
  # 2013-03-09 10:36:26.79

In Oracle you can use SYSTIMESTAMP function that always return the current date and time.

Oracle:

  SELECT SYSTIMESTAMP FROM dual;
  # 09-MAR-13 01.43.01.841000 PM +03:00
 
  -- Wait 3 seconds
  EXEC DBMS_LOCK.SLEEP(3);
 
  -- The current date and time is returned (3 seconds elapsed)
  SELECT SYSTIMESTAMP FROM dual;
  # 09-MAR-13 01.43.04.841000 PM +03:00

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

• PostgreSQL to Oracle Migration Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.