PostgreSQL offers various built-in functions that help us easily manipulate and analyze date and time information in our database. For instance the TO_CHAR() function, TO_TIMESTAMP() function, DATE_PART() function, etc. The TO_TIMESTAMP() is a must-have function for anyone working with PostgreSQL. It is a built-in formatting function that allows us to easily convert a string representation of a date-time into a timestamp data type.

This blog will teach you how to use the TO_TIMESTAMP() function in Postgres via practical examples. So, let’s start!

How to Use TO_TIMESTAMP() Function in Postgres?

For using to_timestamp in Postgres, simply pass in the string you want to convert, along with a format mask that specifies how the date and time are represented. Utilize the following syntax to avail the functionality of the TO_TIMESTAMP() function:

TO_TIMESTAMP(str_timestamp, formatMask);

The “str_timestamp” is a string representing a timestamp value that will be converted to a TIMESTAMP according to the specified “formatMask”. For example, if the given string is in the "YYYY-MM-DD HH:MI:SS" format, then you can use the following format mask: 'YYYY-MM-DD HH:MI:SS'. As a result, the TO_TIMESTAMP will convert the given string into a timestamp according to the provided format mask. The format must be valid, as described in Postgres’ official documentation.

Note: Aside from its basic features, simplicity, and flexibility, the TO_TIMESTAMP also has some advanced features that allow us to customize the conversion process. For instance, you can specify the number of decimal places in the mask to handle timestamps with fractional seconds, etc.

Let’s learn it practically!

Example 1: How to Convert the String Data to TIMESTAMP in Postgres?

Execute the following line of code to convert the given string data into a TIMESTAMP data type:

SELECT TO_TIMESTAMP('2023-01-04 11:13:20', 'YYYY-MM-DD HH:MI:SS');

Here, the second argument represents a format where “YYYY” is used to specify a four-digit year, “MM” for a two-digit month, “DD” for a two-digit month, “HH” for two-digit hours, “MI” for two-digit minutes, “SS” for two-digits seconds:

The output shows that the input string has been converted into a “TIMESTAMPTZ” data type.

Example 2: Adjusting Less Than Four-digits Year

If you specify a year in less than four digits, then the TO_TIMESTAMP() will revise it to the nearest year. For instance, if you specify 95, it will be revised to 1995,14 will be modified to 2014, and so on:

SELECT TO_TIMESTAMP('11 04 23', 'DD MM YY');

The output shows that the “TO_TIMESTAMP” function converted the given “2-digit year” to the nearest four-digits year.

Example 3: How Does the TO_TIMESTAMP Function Treat the Milliseconds/Microseconds?

The TO_TIMESTAMP() addresses the milliseconds/microseconds as seconds while converting a text/string to a timestamp. The TO_TIMESTAMP function specifies the “milliseconds” in the “seconds” field after the decimal point:

SELECT TO_TIMESTAMP('2023-01-04 31:55', 'YYYY-MM-DD SS:MS');

The output shows that the TO_TIMESTAMP function specified the milliseconds in the seconds' field after the decimal part.

Example 4: ERROR: Invalid Value

Passing an invalid format to a TO_TIMESTAMP() function will result in an “Invalid Value” error:

SELECT TO_TIMESTAMP('2023-01-04', 'ABCDYYYY-MM-DD');

The output shows that Postgres threw an “invalid value” error when we specified an inappropriate format.

Example 5: ERROR: Out of Range

In Postgres, you may encounter an “out-of-range value” error while working with the TO_TIMESTAMP() function:

SELECT TO_TIMESTAMP('2023-19-04 11:13:20', 'YYYY-MM-DD HH12:MI:SS');

The TO_TIMESTAMP() function throws a “value out-of-range” error because the “month” field accepts an invalid value(i.e., 19 > 12).

Overall, we can say that the TO_TIMESTAMP() is an essential tool/function for any Postgres developer working with date and time data.

Conclusion

The TO_TIMESTAMP() in Postgres is a built-in function for converting string data into the timestamp data type. Users can easily manipulate and analyze date and time information using this function. It accepts a string representation of a DateTime and converts it into a timestamp data type. This post presents a detailed guide on converting the string data into a timestamp using the Postgres

TO_TIMESTAMP function.