PostgreSQL TO_TIMESTAMP() Function
Last Updated :
26 Sep, 2024
In PostgreSQL, managing and manipulating date and time values is important, especially when they are stored as strings. The to_timestamp function allows us to convert textual representations of dates and times into a valid timestamp format and making it easier to work with them in queries, calculations or comparisons.
In this article, we'll explore the to_timestamp function in PostgreSQL, its syntax, common format patterns and practical examples.
PostgreSQL TO_TIMESTAMP() Function
- The
to_timestamp function in PostgreSQL is used to convert a string or numeric value into a timestamp data type.
- This function is especially useful when dealing with date and time information in formats that need to be transformed into a PostgreSQL timestamp for further operations or storage.
Syntax:
to_timestamp(string text, format text) → timestamp
Explanation:
string: The input value that needs to be converted to a timestamp. It can be a string representing a date and time or a numeric value.
format: A string that specifies the format of the input string or number. This format helps PostgreSQL understand how the input value should be parsed and converted into a timestamp.
Common Format Patterns
The to_timestamp function converts a string into a timestamp by following the format string you've provided. Failing to match the input string with the correct format will lead to unexpected results.
You can construct the format strings and use the below template patterns to format date and time values.
| Pattern | Description |
| HH | Hour of Day (01-12) |
| HH12 | Hour of Day (01-12) |
| HH24 | Hour of Day (00-23) |
| Y | Last Digit of Year |
| YY | Last Two Digits of the Year |
| YYY | Last Three Digits of the Year |
| YYYY | Last Four Digits of the Year |
| Y,YYY | Year in Four Digits with Comma |
| MI | Minute(00-59) |
| SS | Second(00-59) |
| MS | Millisecond (000-999) |
| US | Microsecond (000000-999999) |
| SSSS | Seconds Past Midnight (0-86399) |
AM
or A.M.
or PM
or P.M. | Meridian Indicator (Uppercase) |
am
or a.m.
or pm
or p.m. | Meridian Indicator (Lowercase) |
BC
or B.C.
or AD
or A.D. | Era Indicator (Uppercase) |
bc
or b.c.
or ad
or a.d. | Era Indicator (Lowercase) |
| IYYY | ISO Year (4 and more digits) |
| IYY | Last Three Digits of ISO Year |
| IY | Last Two Digits of ISO Year |
| I | Last Digits of ISO Year |
| MONTH | Full Uppercase Month Name |
| Month | Full Mixed-Case Month Name |
| month | Full Lowercase Month Name |
| mon | Abbreviated Lowercase Month Name |
| MM | Month Number (01-12) |
Examples of Using to_timestamp
Exampe 1: Converting a Simple Date String
- The query
SELECT to_timestamp('04-12-2002', 'DD-MM-YYYY'); converts the date string '04-12-2002', which is in the format 'DD-MM-YYYY', into a timestamp.
- The
to_timestamp function takes two arguments: the date string and the format mask. In this case, the format mask 'DD-MM-YYYY' tells PostgreSQL how to interpret the string, resulting in a valid timestamp that can be used in date and time operations.
Query:
SELECT to_timestamp('04-12-2002', 'DD-MM-YYYY');Output:
Simple data stringExplanation:
- In this example, '04-12-2002' is the input string representing a date.
- The format string 'DD-MM-YYYY' specifies how the day, month, and year are arranged.
- PostgreSQL uses this format to convert the string into a timestamp
Exampe 2: Converting a Date and Time String
- The query
SELECT to_timestamp('2002-12-04 12:30:00', 'YYYY-MM-DD HH24:MI:SS'); converts the string '2002-12-04 12:30:00', which includes both date and time, into a timestamp.
- The format mask
'YYYY-MM-DD HH24:MI:SS' specifies how the string should be parsed, resulting in a precise timestamp suitable for detailed time operations.
Query:
SELECT to_timestamp('2002-12-04 12:30:00', 'YYYY-MM-DD HH24:MI:SS');Output:
Date and time stringExplanation:
- In this case, the input string '2002-12-04 12:30:00' includes both the date and time.
- The format string 'YYYY-MM-DD HH24:MI' ensures that PostgreSQL can correctly interpret the data as year, month, day, hour, minute, and second.
Exampe 3: Using a Compact Date Format
- The query
SELECT to_timestamp('20021204', 'YYYYMMDD') converts the date string '20021204', which is in the compact format 'YYYYMMDD' into a timestamp.
- The format mask
'YYYYMMDD' tells PostgreSQL how to interpret the string, resulting in a timestamp that represents December 4, 2002.
Query:
SELECT to_timestamp('20021204', 'YYYYMMDD');Output:
Date StringExplanation:
The input string '20021204' is a compact date format. By providing the format string 'YYYYMMDD', PostgreSQL is able to correctly parse the year, month, and day. The timestamp will be generated with a default time of 00:00:00.
Conclusion
The to_timestamp function in PostgreSQL is a versatile tool for converting date and time values stored as strings into proper timestamp data types. By specifying the correct format, you can convert a wide variety of date and time formats into timestamp values that can be used for calculations, comparisons, or storage.
Similar Reads
PostgreSQL - CURRENT_TIMESTAMP Function PostgreSQL provides the 'CURRENT_TIMESTAMP() function, which returns the current date and time, along with the time zone, at the precise moment the transaction begins. This function is often used for tracking the creation or modification of records in a database.Here, we’ll look into the syntax, usa
2 min read
PostgreSQL - Timestamp Data Type PostgreSQL supports two primary temporal data types to store date and time: TIMESTAMP (without timezone) and TIMESTAMPTZ (with timezone). Understanding these data types is crucial for managing date and time effectively across different regions and time zones. In this article, we will explain the TIM
4 min read
PostgreSQL - CURRENT_TIME Function The PostgreSQL CURRENT_TIME function returns the current time and the current time zone. This function is handy when you need to work with time-sensitive data in your database applications.Let us better understand the CURRENT_TIME Function in PostgreSQL from this article.SyntaxCURRENT_TIME(precision
2 min read
CURRENT_TIMESTAMP() Function in SQL Server In SQL Server, the CURRENT_TIMESTAMP function is a widely used feature for retrieving the current date and time directly from the database server. It returns the exact moment when the SQL query is executed in the YYYY-MM-DD hh:mm:ss.mmm format.In this article, We will learn about CURRENT_TIMESTAMP()
3 min read
PostgreSQL - NOW() Function The NOW() function in PostgreSQL is a powerful and essential tool for retrieving the current date and time. This is particularly useful when recording actions like adding or updating records in our database. Using PostgreSQL NOW() function, we can efficiently track when events occur, making the data
4 min read
How to Extract Date From a TimeStamp in PostgreSQL PostgreSQL is a powerful open-source relational database management system (RDBMS). PostgreSQL is well-known for its feature-rich capabilities, standardization, and adaptability. It supports a variety of data types, complex SQL queries, and ACID properties. PostgreSQL offers scalability and durabili
4 min read
PostgreSQL - DATE_TRUNC Function The PostgreSQL DATE_TRUNC() function is a powerful tool that helps us truncate a timestamp or interval to a specified level of precision. It is particularly useful when dealing with time-based data for effective data grouping and manipulation, making it ideal for reporting, analytics, and queries wh
4 min read
PostgreSQL Date Functions PostgreSQL is widely recognized for its comprehensive support for date and time manipulations, making it an excellent choice for applications requiring precise time management and complex calculations. This article explores the core PostgreSQL date functions, covering how to retrieve the current dat
4 min read
PostgreSQL - TIME Data Type In PostgreSQL, the TIME data type is essential for applications that require precise time tracking, such as scheduling systems and event logging. This data type allows for accurate time-based entries without storing date information. PostgreSQL’s TIME data type also supports fractional seconds for u
4 min read
PostgreSQL - EXTRACT Function In PostgreSQL, the EXTRACT() function is a powerful tool used to retrieve specific components of a date or time value. Whether you need to query for a particular year, month, day, or even more detailed time attributes, EXTRACT() can help you extract these fields from date and time values efficiently
2 min read