Remove seconds from current date in Redshift (PostgreSQL)
10,573
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
);
Related videos on Youtube
Comments
-
Serenthia almost 2 years
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?
-
Serenthia over 10 yearsIncredible, thank you - somehow this function passed me by as I was perusing the docs.