Today I Learned

A Hashrocket project

Convert A String To A Timestamp In PostgreSQL

If you have a string that represents a point in time, there are a couple ways that you can convert it to a PostgreSQL timestamptz value.

If the string is in ISO 8601 format, then it can be simply cast to timestamptz.

> select '2018-10-24'::timestamptz;
      timestamptz
------------------------
 2018-10-24 00:00:00-05

A more general purpose approach is to use the to_timestamp function.

> select to_timestamp('2018-10-24', 'YYYY-MM-DD');
      to_timestamp
------------------------
 2018-10-24 00:00:00-05

The first argument is our string-to-be-converted in whatever format. The second argument is another string describing in what format that string is.

Note: Both of these approaches produce a timestamptz value.

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.