Discard millisecond part from timestamp

Question

How can I discard/round the millisecond part, better if the second part is also removed from a timestamp w/o timezone ?

Acheron

Answer 1

A cast to timestamp(0) or timestamptz(0) rounds to full seconds:

SELECT now()::timestamp(0);

Fractions are not stored in table columns of this type.

date_trunc() truncates (leaves seconds unchanged) - which is often what you really want:

SELECT date_trunc('second', now()::timestamp);

Epithalamium

Note that timestamp(0) and timestamptz(0) will round rather than truncate. Use date_trunc if you want to preserve the rest of the timestamp as is. – Kultur

Doesn't work, it throws an error [Amazon](500310) Invalid operation: Assert – Uncleanly

@SlavikMeltser: The expressions in my answer work in Postgres. 100 %. Your problem obviously has a different cause. – Epithalamium

@ErwinBrandstetter I am using Amazon Redshift. Probably they have removed this feature in Redshift. – Uncleanly

@SlavikMeltser: Redshift is not Postgres. It was forked from Postgres at version 8.0 - a very long time ago. – Epithalamium

When I run SELECT now()::timestamp(0); in Datagrip (connecting to a postgres DB), it still shows the milliseconds, even though they are all zero. I'd like to know how to remove that as well. Might be a Datagrip setting I haven't found yet. Interestingly, running SELECT date_trunc('second', now()::timestamp); does not show milliseconds. – Carousal

@Purplejacket: Sounds like a matter of display in the sphere of Datagrip. – Epithalamium

@Carousal I ran that exact query in Datagrip, and got it without milliseconds. FWIW I'm using Datagrip 2018.2 on Ubuntu 18.04, querying a PostgreSQL 9.5.15 server. I'm curious if either your Datagrip or postgres versions are older, and if an update of either/both has rectified your issue. – Anthea

Answer 2

Discard milliseconds:

SELECT DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp);

2019-08-23 16:42:43

Discard seconds:

SELECT DATE_TRUNC('minute', CURRENT_TIMESTAMP::timestamp);

2019-08-23 16:42:00

Acceptor

Answer 3

if you just want time, here is the code for postgres

SELECT DATE_TRUNC('second', CURRENT_TIMESTAMP::timestamp)::time;

it will return 'time without time zone' data type

Apartheid

Answer 4

Try select date_trunc('second',ur_date_field) as ur_date_field.

It will return results like 2022-06-21 12:56:41.

Crackbrain