22

Is there a way to create a table with a timestamp column defaulting to now?

Any of the following succeeds on creation but fails on insertion.

CREATE TABLE MyTable
(
device_id         VARCHAR(64) NOT NULL encode lzo,
app_id            VARCHAR(150) NOT NULL distkey encode lzo,
retrieval_date    TIMESTAMP without time zone default (now() at time zone 'utc') encode lzo
)
diststyle key
compound sortkey(app_id,device_id);

Then on insert:

     insert into MyTable (device_id,app_id) values('a','b');
INFO:  Function "timezone(text,timestamp with time zone)" not supported.
INFO:  Function "now()" not supported.
ERROR:  Specified types or functions (one per INFO message) not supported on Redshift tables.

I tried other flavors as below but all fail.

a) Tried with now in single quotes 'now' , create succeeded but failed with another error

b) Tried without the timezone, create succeeded, insert failed.

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
isaac.hazan
  • 3,772
  • 6
  • 46
  • 76

1 Answers1

42

You can use SYSDATE or GETDATE() to put a current timestamp value. Here is an example.

dev=> create table my_table (id int, created_at datetime default sysdate);
CREATE TABLE

dev=> insert into my_table (id) values (1);
INSERT 0 1

dev=> select * from my_table;
 id |        created_at
----+---------------------------
  1 | 2016-01-04 19:07:14.18745
(1 row)
Masashi M
  • 2,679
  • 21
  • 22
  • 1
    How do you set the time to a timezone different than UTC which is the Redshift default? – Jorge Mar 31 '16 at 15:53
  • 2
    DEFAULT CONVERT_TIMEZONE('Asia/Kolkata', GETDATE()) http://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html – Deepak Jul 14 '17 at 07:12