Back to list PostgreSQL How to Convert a String to a Timestamp in PostgreSQL Database: PostgreSQL Operators:TO_TIMESTAMP() Problem: You’d like to convert a string containing datetime information to a timestamp in PostgreSQL. Let’s convert a string containing date, time, and time zone information to the timestamptz data type. Solution: We’ll use the TO_TIMESTAMP() function. Here’s the query you would write: SELECT TO_TIMESTAMP('2018/08/27/15:23:45', 'YYYY/MM/DD/HH24:MI:ss') AS new_timestamptz; Here’s the result of the query: new_timestamptz 2018-08-27 15:23:45+02 Discussion: Use the PostgreSQL function TO_TIMESTAMP() when you want to convert a string containing date and time data to the timestamp data type. This function takes two arguments: a string containing a date and time (in our example, the text '2018/08/27/15:23:45') and the input format (in our example, ‘YYYY/MM/DD/HH24:MI:ss’). The input format indicates how the characters in the string should be converted. Here are the main elements from the above pattern: YYYY represents a 4-digit year. MM represents a 2-digit month. DD represents a 2-digit day of the month. HH24 represents a 2-digit hour (from 00 to 23). MI represents a 2-digit minute (from 00 to 59). ss represents a 2-digit second (from 00 to 59). Note that we use slashes (/) as date part delimiters and colons (:) as time part delimiters. You can find a complete list of datetime pattern elements in the PostgreSQL documentation. Notice that the input format is a string. The function TO_TIMESTAMP() returns a timestamptz value with time zone information. In our example, the text date and time '2018/08/27/15:23:45'was converted to the timestamp value 2018-08-27 15:23:45+02. The timestamp data type is more readable. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Extract the Week Number from a Date in PostgreSQL How to Find the Interval Between Two Dates in PostgreSQL How to Get the Day of the Year from a Date in PostgreSQL How to Convert a String to a Date in PostgreSQL Tags: PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.