CREATE TABLE with INTERVAL option in DEFAULT clause
I am trying to create a table as shown below, but the statement fails with a 3706 error code due the usage of "INTERVAL" option in the DEFAULT TIMESTAMP clause.
Is there any other way to add an interval to the default timestamp clause at the time of defining the table?
To give a bit of background, I am trying to default all timestamp values on this column to a specific setting (say Pacific time to Eastern time) without having to declare the column as "TIMESTAMP WITH TIMEZONE" (due to application related limitations).
CREATE MULTISET TABLE TEST_TBL , NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( ID INTEGER NOT NULL, DESCRIPTION VARCHAR(255), CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP + INTERVAL '3' HOUR ) NO PRIMARY INDEX;
Also, I am noticing the following with our System Time / TimeZone. I understand that the best way is to sync up the System Time / TimeZone to appropriate values, but is there a way to manually convert a given timestamp into the required timezone by ignoring system setting - For ex: System TimeZone is "GMT", but System Clock is set to "Pacific" time, then what's the best way to convert the timestamp values to say "Eastern" time?
select current_timestamp(0), current_timestamp(0) at 'GMT'
Current TimeStamp(0) /* Note: the value returned below is in Pacific Time */ Current TimeStamp(0) AT TIME ZONE 'GMT' /* System Time Zone seems to have been set to 'GMT' instead of 'Amerca Pacific' */ 2016-06-13 12:26:19 2016-06-13 12:26:19