103

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Dipro Sen
  • 4,350
  • 13
  • 37
  • 50

4 Answers4

184

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);
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 19
    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. – karlgold Jan 07 '15 at 20:53
  • Doesn't work, it throws an error `[Amazon](500310) Invalid operation: Assert` – Slavik Meltser Oct 24 '17 at 10:54
  • @SlavikMeltser: The expressions in my answer work in Postgres. 100 %. Your problem obviously has a different cause. – Erwin Brandstetter Oct 24 '17 at 11:56
  • @ErwinBrandstetter I am using Amazon Redshift. Probably they have removed this feature in Redshift. – Slavik Meltser Oct 25 '17 at 15:41
  • 1
    @SlavikMeltser: Redshift is not Postgres. It was forked from Postgres at version 8.0 - a *very* long time ago. – Erwin Brandstetter Oct 28 '17 at 02:02
  • 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. – Purplejacket Dec 26 '18 at 22:00
  • @Purplejacket: Sounds like a matter of display in the sphere of Datagrip. – Erwin Brandstetter Dec 27 '18 at 13:38
  • 1
    @Purplejacket 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. – Doktor J Mar 26 '19 at 21:25
24

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

Yuriy Rypka
  • 1,937
  • 1
  • 19
  • 23
5

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

Shriganesh Kolhe
  • 253
  • 4
  • 5
1

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

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

shriek
  • 5,605
  • 8
  • 46
  • 75
user19395282
  • 11
  • 1