postgres - cast timestamp format list

Question

In postgres when i do cast(varchar_col as timestamp) how do i know which formats (ie yyyymmdd, yyyy-mm-dd...etc) are supported?

Answer 1

Not great research on your end, because it's right there in the Postgres 8.4 docs in the section of the eponymous name:

https://www.postgresql.org/docs/8.4/datatype-datetime.html

Valid input for the time stamp types consists of the concatenation of a date and a time, followed by an optional time zone, followed by an optional AD or BC. (Alternatively, AD/BC can appear before the time zone, but this is not the preferred ordering.)

So, you need to combine all input format entries from the "Date Input" table and from the "Time Input" table, which are too many to list sensibly in an answer. Makes no sense either – that's what official documentation is for.

For a more comprehensive documentation on how dates and times are parsed from strings, that's officially documented as well:

https://www.postgresql.org/docs/8.4/datetime-input-rules.html