1

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

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
John Alexander Betts
  • 4,718
  • 8
  • 47
  • 72

2 Answers2

6

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
5

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228