In PostgreSQL, the TO_TIMESTAMP() is a built-in function that accepts a string and a format as arguments and converts the given string to a TIMESTAMP based on the specified format. It retrieves a TIMESTAMP along with a time zone.

This write-up will consider some examples to provide a detailed guide on how to use the TO_TIMESTAMP() function in PostgreSQL. So, let’s get started!

How to Use TO_TIMESTAMP() Function in PostgreSQL?

The following snippet will illustrate the syntax of the TO_TIMESTAMP function:

TO_TIMESTAMP(string, format);

Here, the string represents a timestamp that needs to be converted to a timestamp based on the specified format.

The format must be a valid format based on which the given string will be converted into a timestamp. The valid formats for the TO_TIMESTAMP() function are listed below:

• CC: Specifies a century in two digits.
• YYYY: Indicates the Year in four digits/figures.
• YYY: To specify the Year in three digits.
• YY: Specifies the Year in two digits.
• Y: To specify only the last digit of the year.
• Y,YYY: Specifies the Year in four digits; the first digit will be separated with a comma.
• IYYY: ISO standard 4-digit year.
• IYY: ISO standard 3-digit year.
• IY: Specifies the year in two digits ISO standard.
• I: Indicates only the last digit of the year according to the ISO standard.
• Q: Used to specify a quarter (1 quarter = 3 months, e.g., Jul-Sep).
• MM: Specifies a month in two digits(01-12; e.g., JAN = 01, DEC =12).
• MONTH: Month in Uppercase Letters.
• Month: Capitalized(first letter capital) month name.
• month: Month name in lowercase.
• MON: First three letters of a month in uppercase (e.g., DEC).
• Mon: First three letters of the month(capitalized), e.g., Nov, Dec, etc.
• mon: First three letters of the month in lowercase, e.g., nov, dec, etc.
• RM: To specify the month in uppercase roman numerals e.g., IX, X, XI, etc.
• rm: Specifies the month in lowercase roman numerals e.g., ix, x, xi, etc.
• W: Week number of month (1-5).
• WW: Week number of year (1-53).
• IW: Week number according to ISO 8601 standards.
• DAY: Specifies a day in uppercase letters.
• Day : Specifies a capitalized(first letter capital) day.
• day: Specifies the day name of day in lowercase letters.
• DY: Abbreviated day name in uppercase letters.
• Dy: Abbreviated capitalized(First letter capital) day name.
• dy: Abbreviated day name in lowercase letters.
• DDD: Day of the year (001-366).
• IDDD: Day of a year according to ISO.
• DD: Day of a month(01-31).
• D: Day of week (1-7, here 1 represents Sunday, 2 for Monday, … and 7 represents Saturday)
• ID: Day of the week according to ISO year (1-7, here 1 represents Monday, 2 represents Tuesday, and so on.)
• J: Julian day; i.e. no. of days since Nov 24, 4714 BC.
• TZ: Time zone in uppercase letters.
• tz: Time zone in lowercase letters.
• HH or HH12: Hours (01-12).
• HH24: Hours(00-23).
• MI: Minutes (00-59).
• SS: Seconds (00-59).
• MS: Milliseconds (000-999).
• US: Microseconds (000000-999999).
• SSSS: Seconds past midnight(0-86399).
• A.M., AM, P.M, PM, a.m, am, p.m, pm: Meridian indicator.
• AD, A.D, a.d, ad: AD indicator.
• BC, B.C, b.c, bc: BC indicator.

Example #1: How to Convert a String to 'YYYY-MM-DD HH:MI:SS' Format Using TO_TIMESTAMP() function?

Let’s consider the following query to understand the working of the TO_TIMESTAMP() function:

SELECT TO_TIMESTAMP('2022-09-20 12:30:12', 'YYYY-MM-DD HH:MI:SS');

Output clarifies that the TO_TIMESTAMP() function retrieves the timestamp along with the time zone. The returned timestamp is formatted as per the given format.

Example #2: How to Convert a String to 'YYYY-MM-DD HH24:MI' Format Using TO_TIMESTAMP() Function?

In this example, we will utilize the 'YYYY-MM-DD HH24:MI' format, so the given string will be converted accordingly:

SELECT TO_TIMESTAMP('2022-09-20 12:30:12', 'YYYY-MM-DD HH24:MI');

The resultant output clarifies that the hours are converted according to the “HH24” format. Since the specified format didn’t utilize the SS, so the resultant timestamp will initialize the seconds with 00.

Example #3: How to Convert a String to a Specific Format Using the TO_TIMESTAMP() function?

If you specify a year in less than four digits within the given string, then the TO_TIMESTAMP() function will convert it to the nearest year. For instance, 97 will be converted to 1997, 22 will be converted to 2022, etc.

SELECT TO_TIMESTAMP('21-10-20 12:30:12.014.29282', 'YY/MM/DD HH:MI:SS.MS.US');

The two digit year i.e., ‘21’ has been converted into the nearest year, i.e., 2021. Let’s consider one more example to understand this concept in a better way:

SELECT TO_TIMESTAMP('91-10-20 12:30:12.014.29282', 'YY/MM/DD HH:MI:SS.MS.US');

The output authenticates the working of the TO_TIMESTAMP() function.

Example #4: How to Use the TO_TIMESTAMP() Function on Table’s Data?

Let’s create a table named “employee_details” having three columns: emp_id, emp_name, and emp_joining_date:

CREATE TABLE employee_details(
emp_id INT PRIMARY KEY,
emp_name TEXT,
emp_joining_date TEXT);

The employee_details table with the respective columns has been created successfully. Let’s insert some records into the employee_details table:

INSERT INTO employee_details(emp_id, emp_name, emp_joining_date)
VALUES (1, 'Joe', '2020-04-01'),
(2, 'Mike', '2020-04-15'),
(3, 'Seth', '2021-01-01');

Three records have been inserted into the employee_details table. Let’s use the SELECT statement to see the content of the employee_details table:

SELECT * FROM employee_details;

Now we will utilize the TO_TIMESTAMP() function on the employee_details table to convert the emp_joining_date column to a TIMESTAMP:

SELECT TO_TIMESTAMP(emp_joining_date, 'YYYY-MM-DD') 
FROM employee_details;

This is how you can utilize the TO_TIMESTAMP() function on the table’s data.

Example #5: Out of Range Value?

Converting the given string “2021-10-12 25:30:10” to 'YYYY-MM-DD HH24:MI:SS' format using the TO_TIMESTAMP() function will produce unexpected results:

SELECT TO_TIMESTAMP('2021-10-12 25:30:10', 'YYYY-MM-DD HH24:MI:SS');

We encountered an error because the string contains an out-of-range value for the hours' field i.e., 25. The value for the ‘HH24’ field must be between 00-23.

This is how the TO_TIMESTAMP() function works in PostgreSQL.

Conclusion
The TO_TIMESTAMP() is a built-in function in Postgres that accepts a string and a format as arguments. Consequently, the TO_TIMESTAMP() function converts the given string to a TIMESTAMP based on the specified format and retrieves a TIMESTAMP along with a time zone. Several scenarios were considered in this write-up to explain how the TO_TIMESTAMP() function works in Postgres.