Getting a UTC date as a default value

oracle

Solution

Try this:

CREATE TABLE MY_TEST
  (ID NUMBER,
   CURR_TIMESTAMP TIMESTAMP DEFAULT SYSTIMESTAMP,
   UTC_TIMESTAMP  TIMESTAMP DEFAULT SYS_EXTRACT_UTC(SYSTIMESTAMP));

INSERT INTO MY_TEST(ID) VALUES(1);

SELECT * FROM MY_TEST;

Share and enjoy.

Edit: for the fun of it I decided to try and get this to include the correct timezones. I found that just doing a SYS_EXTRACT_UTC(SYSTIMESTAMP) into a TIMESTAMP WITH TIME ZONE column changed the time portion of the value correctly but left the time zone alone. After a bit of mucking about I came up with the following:

CREATE TABLE RPJ_TEST
  (ID NUMBER,
   CURR_TIMESTAMP TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,
   UTC_TIMESTAMP  TIMESTAMP WITH TIME ZONE
      DEFAULT TO_TIMESTAMP_TZ(TO_CHAR(SYS_EXTRACT_UTC(SYSTIMESTAMP)) || ' 00:00',
                              'DD-MON-YYYY HH:MI:SS.FF6 PM TZH:TZM'));

Hideous, but it does get the timezone of the UTC time column right.

Resistance is useless.

Problem

In SQL Server is possible to create a table like this: ``` create table test ( [TimeStamp_test] [datetime] NOT NULL DEFAULT (getutcdate()) ); ``` Do we have any thing similar to `getutcdate()` in `oracle`? I am aware of using `SELECT SYS_EXTRACT_UTC FROM DUAL` (i.e using a trigger on insert to this table). Let me know if there is other easy option available, thanks.

Original source