How to convert "string" to "timestamp without time zone"

String representation of a timestamp (= timestamp without time zone) depends on your locale settings. Therefore, to avoid ambiguities leading to data errors or Postgres coughing up an exception, you have two options:

1.) Use ISO 8601 format, which works the same with any locale or DateStyle setting:

'2013-08-20 14:52:49'

You may have to cast the string literal explicitly where the data type cannot be derived from context, depending on the use case:

'2013-08-20 14:52:49'::timestamp

2.) Convert the string to timestamp using to_timestamp() with a matching template pattern:

to_timestamp('20/8/2013 14:52:49', 'DD/MM/YYYY hh24:mi:ss')

This returns timestamptz, assuming the current timezone setting. Typically (like in an assigmment) the type is coerced accordingly. For timestamp, this means that the time offset is truncated and you get the expected value.Again, if the target type cannot be derived from context, you may have to cast explicitly:

to_timestamp('20/8/2013 14:52:49', 'DD/MM/YYYY hh24:mi:ss')::timestamp

Since that simply strips the time offset, it results in the expected value. Or use the AT TIME ZONE construct with a time zone of your choosing:

to_timestamp('20/8/2013 14:52:49', 'DD/MM/YYYY hh24:mi:ss') AT TIME ZONE 'UTC'

While the target time zone is the same as your current timezone setting, no transformation takes place. Else the resulting timestamp is transposed accordingly. Further reading:

• Ignoring time zones altogether in Rails and PostgreSQL

To convert a string into a timestamp without timezone, for Postgresql, I use the above

SELECT to_timestamp('23-11-1986 09:30:00', 'DD-MM-YYYY hh24:mi:ss')::timestamp without time zone;