sqlpostgresql

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

Solution

  • 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: