How to truncate seconds from a column (timestamp) in PostgreSQL without using date_trunc function

Question

I am using PostgreSQL 9.3.19, earlier I have made the following Query

SELECT DATE_TRUNC('minute', some_date) FROM some_table;

This was working fine but I got to know that index made on some_date column will be futile because indexes doesn't work with DATE_TRUNC(), Index created was as follows :

CREATE INDEX ON some_table USING BTREE(DATE_TRUNC('minute', some_date));

So is there any other way to truncate seconds without affecting my present Index and without using DATE_TRUNC in PostgreSQL 9.3.19 ?

No I am not allowed to change the working Index. I want some other way to truncate seconds @a_horse_with_no_name — Aadit
– Aadit, Commented Aug 30, 2018 at 9:38
I did, and I don't understand it. What is the problem with the query you have? Does it throw an error? If yes, edit your question and add the exact error message. If it doesn't throw an error but returns the wrong data, then you need to explain what input data you have and what you expect from the query. — user330315
– user330315, Commented Aug 30, 2018 at 9:47
If it is a performance problem then edit your question and add the execution plan generated using explain (analyze, buffers) as formatted text, no screen shots please — user330315
– user330315, Commented Aug 30, 2018 at 9:47

denbon05 · Accepted Answer · 2021-12-28 13:02:41Z

3

SELECT date_trunc('seconds', '2021-12-28 14:00:03.933438'::timestamp);
-- RESULT 2021-12-28 14:00:03

as here

answered Dec 28, 2021 at 13:02

denbon05

414 bronze badges

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

1 Comment

Cezary Butler Over a year ago

It looks like your answer is just what original author of the question was not looking for. Please look at the question again.

Collectives™ on Stack Overflow

How to truncate seconds from a column (timestamp) in PostgreSQL without using date_trunc function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related