Search code examples
postgresqlenumsbuilt-in

Does Postgres have a system table listing precision values for date_trunc?

The list of valid parameters for date_truc found here:

https://www.postgresql.org/docs/10/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC

microseconds
milliseconds
second
minute
hour
day
week
month
quarter
year
decade
century
millennium

,are they found listen in a system table somewhere? If so, where?

Solution

  • The supported tokens are hard-coded and exposed only through a lookup function, so there's no way to retrieve the whole list.

    The full list is quite a bit longer than the docs suggest, e.g. just for milliseconds, all of these work:

    select
  date_trunc('ms', now()),
  date_trunc('msec', now()),
  date_trunc('msecs', now()),
  date_trunc('msecond', now()),
  date_trunc('mseconds', now()),
  date_trunc('millisecond', now()),
  date_trunc('milliseconds', now())

    In fact, only the first 10 characters of the token are stored, so this works too:

    select date_trunc('milliseconzzzzzzzzzzzzzzzzzzzzzzzzzz', now())