CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
U W
U W

Reputation: 1290

Remove seconds from current date in Redshift (PostgreSQL)

In Amazon Redshift I'm looking to convert the current timestamp to have 0 seconds. That is go from this:

2013-12-17 12:27:50

to this:

2013-12-17 12:27:00

I have tried the following:

SELECT dateadd(second, -(date_part(second, getdate())), getdate());
ERROR:  function pg_catalog.date_add("unknown", double precision, timestamp without time zone) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

SELECT dateadd(second, -cast(date_part(second, getdate()) as double precision), getdate());
ERROR:  function pg_catalog.date_add("unknown", double precision, timestamp without time zone) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

SELECT getdate() - date_part(second, getdate());
ERROR:  operator does not exist: timestamp without time zone - double precision
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

I'm probably missing a very simple way of doing this! Does anyone have any suggestions, please?

Upvotes: 5

Views: 12596

Answers (3)

Tomasz Tybulewicz
Tomasz Tybulewicz

Reputation: 8647

It's easiest to use the date_trunc() function, but that will work only while selecting: 

SELECT date_trunc('minute', TIMESTAMP '2013-12-17 12:27:00');

You may preprocess data before loading data into the redshift DB, or use intermediary table and then use INSERT INTO...SELECT statement: 

INSERT INTO destination_table (
    SELECT date_trunc('minute', date_column), other_columns_here    
    FROM source_table
);

Upvotes: 7

Frank Heikens
Frank Heikens

Reputation: 126991

Check date_trunc()

SELECT date_trunc('minute', TIMESTAMP '2013-12-17 12:27:00');

Upvotes: 0

xdazz
xdazz

Reputation: 160833

You could format the date with:

select to_char(now(), 'YYYY-MM-DD HH24:MI:00');

Upvotes: -1

Related Questions