CODEWITHSUNDEEP
sqlpostgresql
John Alexander Betts
John Alexander Betts

Reputation: 5206

Disadvantages of using date_trunc

Several years ago I heard that truncate dates in sql is not a good practice. Is that true?

I need to get the value of a date type column from a table in "yyyy-MM-dd" format, the date is currently stored in the table with the "yyyy-MM-dd hh:mm:ss" format

Upvotes: 2

Views: 2299

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

It's bad practice to store that in any kind of format, since you should store it as timestamp or timestamp with time zone not as text or varchar.

Then, to get the date, just cast:

SELECT col::date

Be aware that the "date" depends on your current time zone setting.

See:

Upvotes: 6

Craig Ringer
Craig Ringer

Reputation: 324475

There is nothing wrong with using date_trunc under the right circumstances.

The main issue is where b-tree indexes are involved. If you have an index on some_timestamp and you search for date_trunc('day', some_timestamp) = DATE '2012-01-01', PostgreSQL can't use the index.

(It could theoretically rewrite the date_trunc expression into an indexable range predicate, but it doesn't).

If you instead write:

some_timestamp >= DATE '2012-01-01' AND some_timestamp < DATE '2012-01-02'

then it's b-tree indexable. (Note that I intentionally did not use BETWEEN because it's both-inclusive).

It's sometimes useful to create an expression index, e.g.

create index blahindex on blahtable (date_trunc('day', some_timestamp));

but as each extra index has a maintenance cost it's better to just write your predicates to be easily usable with normal column indexes where possible.

Upvotes: 7

Related Questions