Geek asks and answers

Getting UTC Date as Default

In SQL Server, you can create a table as follows:

create table test (
  [TimeStamp_test] [datetime] NOT NULL DEFAULT (getutcdate())
);

Do we have something like getutcdate()in oracle?

I know the use SELECT SYS_EXTRACT_UTC FROM DUAL(i.e. using a trigger to insert into this table).

Let me know if there is another option available, thanks.

+3
source share
2 answers

Try the following:

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 fun, I decided to try and enable this to enable the correct time zones. I found that just executing SYS_EXTRACT_UTC (SYSTIMESTAMP) in the TIMESTAMP WITH TIME ZONE column changed the time part of the value correctly, but left only the time zone. After a bit of trash, 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'));

, UTC.

.

+5

UTC:

CREATE TABLE TEST (ID NUMBER, UTC_TIMESTAMP TIMESTAMP WITH TIME ZONE DEFAULT FROM_TZ(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'UTC'));
0

More articles:


All Articles