postgresql - NOW() but less specific

Question

When I use SELECT NOW(); I'll get an output like this:

          now              
-------------------------------
2019-09-09 18:55:38.794006-05
(1 row)

I want it like this:

          now              
-------------------
2019-09-09 18:55:38
(1 row)

How do I make NOW() round up/down accordingly? I tried SELECT NOW()::timestamptz(0); but it keeps adding -05 to the end of the time :(

Possible duplicate of [Discard millisecond part from timestamp](https://stackoverflow.com/questions/10213190/discard-millisecond-part-from-timestamp) — Code4R7, Sep 09 '19 at 19:21

jimmu · Answer 1 · 2019-09-09T19:19:19.370

3

You can to_char

select to_char(now(), 'YYYY-MM-DD HH:MI:SS')

or use date_truc to retain datatype timestamp with time zone

select date_trunc('minute', now());

edited Sep 09 '19 at 19:19

answered Sep 09 '19 at 19:10

jimmu

Looks like it works nicely, but trying to add that to a TIMESTAMP column gives me: ERROR: column "date_of_receipt" is of type timestamp without time zone but expression is of type text – coldslaw Sep 09 '19 at 19:14
J Spratt - My field is TIMESTAMP not timestamptz – coldslaw Sep 09 '19 at 19:22

Code4R7 · Accepted Answer · 2019-09-09T19:16:06.630

0

SELECT NOW()::timestamp(0)

edited Sep 09 '19 at 19:16

answered Sep 09 '19 at 19:12

Code4R7

2 Answers2