How to truncate seconds from timestamp in postgres?

Question

I have the following field in my table:

VISIT_DATE TIMESTAMP(0)

And it is holding time like that:

2022-01-13 11:04:15

Could someone tell me is it possible to cut off that seconds? I really don't need them. I want to hold time in the following format:

2022-01-13 11:04

If this is only a formatting issue then SELECT to_char(VISIT_DATE, 'yyyy-mm-dd hh24:mi') — Stefanov.sm
– Stefanov.sm, Commented Dec 13, 2021 at 10:14

Jim Jones · Accepted Answer · 2021-12-13 10:20:27Z

5

Either truncate the timestamp by minutes using date_trunc, which will return a timestamp without seconds, or use to_char if it is only about formatting the output:

SELECT date_trunc('minute',VISIT_DATE) FROM t;
SELECT to_char(VISIT_DATE,'yyyy-mm-dd hh24:mi') FROM t;

Demo:

Using date_trunc (result as timestamp):

SELECT date_trunc('minute','2022-01-13 11:04:15'::timestamp);

     date_trunc      
---------------------
 2022-01-13 11:04:00
(1 Zeile)

Using to_char (result as text):

SELECT to_char('2022-01-13 11:04:15'::timestamp,'yyyy-mm-dd hh24:mi');

     to_char      
------------------
 2022-01-13 11:04

edited Dec 13, 2021 at 10:20

answered Dec 13, 2021 at 10:15

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Karord Over a year ago

It is working when someone want to retrieve data from database. But I want simple timestamp field in my table without that seconds at all. Is it possible? Or it is just stupid question.

Jim Jones Over a year ago

@Karord I see. Well, you cannot change the data type to not store seconds. What you can do is to se them to 0 in your insert/upates or just ignore them in query time as I demonstrated above. To really store these timestamps in the format you want you'd need to store them as text, which isn't a good idea :) cheers

Collectives™ on Stack Overflow

How to truncate seconds from timestamp in postgres?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related