How to Format a TIMESTAMP() Function in PostgreSQL 101 Guide

Often organizations store timestamp data to understand the progress of business operations better. However, these timestamp data are stored in a ‘string’ data type. This leads to multiple issues while analyzing data since ‘string’ is not ideal for time-series data analysis.

It is ideal for converting ‘string’ into ‘DateTime’ format to ensure a seamless workflow for obtaining insights. You can do the transformation using the PostgreSQL To_Timestamp(). On converting the DateTime format, you can easily extract elements like a month, time, year, day, and more from the timestamp, which is usually tricky while working with the ‘string’ type timestamp data.  

This article will teach you the method PostgreSQL TO_TIMESTAMP, its syntax, examples, and more.

Prerequisites

Understanding of database

What is PostgreSQL?

Developed in 1996, PostgreSQL is an open-source relational database management system that can support JSON and SQL queries. It is also one of the best choices for data warehouses for many companies due to its scalability and flexibility. PostgreSQL includes many features that help businesses build special applications, maintain data integrity, and support fault tolerance. With PostgreSQL, users can define their data types and create custom functions using different programming languages.

Functions in PostgreSQL

Functions in PostgreSQL, also called stored procedures, allow you to carry out operations that usually take many queries and round them in a single function within the database. Functions enable reusability, allowing the application to interact directly with your stored procedures rather than a middle tier or duplicating code.

Functions in PostgreSQL can be created in many languages such as SQL, PL/pgSQL, Python, C, and more.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [...]
      RETURN { variable_name | value }
   END; LANGUAGE plpgsql;

From above:

• Function-name: It specifies the name of the function.
• [OR REPLACE]: This option allows you to modify the existing function.
• Return: It is a return statement that every function consists of. Return specifies the data type you will return from the function. The return data type can be base, composite, or domain type, or it can be referenced to the table column type. 
• Function-body: It contains the executable part.
• AS: It is the keyword used to create a standalone function.
• Plpgsql: The name of the language in which the function is implemented.

The below example creates and calls a standalone function. It returns the total no of records in the COMPANY table. Use the COMPANY table with the below records.

testdb# select * from COMPANY;

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The function totalRecords() is defined as follows:

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
	total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

When the above query is executed, the output is

testdb# CREATE FUNCTION

Execute a call of this function using the below command and check the records in the COMPANY table.

testdb=# select totalRecords();

PostgreSQL To_Timestamp() in Overview

Understanding TO_TIMESTAMP in PostgreSQL

The TO_TIMESTAMP function in PostgreSQL is a function that is used to convert a string type into a timestamp type value according to the specified format. It can convert your string into different data and time formats, but one at a time.

In this tutorial, you will learn to use the PostgreSQL TO_TIMESTAMP function for converting a string to a timestamp based on a particular format.

Syntax

TO_TIMESTAMP(timestamp, format)

ARGUMENTS:

1. Timestamp: The argument represents the string (TEXT type) with date/time value to convert into the timestamp type value by applying the format specified as the second argument.
2. Format: It is the format for the timestamp argument.

The TO_TIMESTAMP() function returns the timestamp type along with the timezone.

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)

For more Timestamp template patterns, you can refer to PostgreSQL’s official doc.

The default format of the timestamp value is as follows:

SELECT TO_TIMESTAMP('2022-07-28 07:50:10', 'YYYY-MM-DD HH:MI:SS');

SELECT now()::timestamp;

SELECT current_timestamp;

SELECT now();

Output:

      to_timestamp      
------------------------
 2022-07-28 07:50:10+00
(1 row)

            now             
----------------------------
 2022-07-29 06:24:39.878397
(1 row)

       current_timestamp       
-------------------------------
 2022-07-29 06:24:39.878755+00
(1 row)

              now              
-------------------------------
 2022-07-29 06:24:39.878932+00
(1 row)

The PostgreSQL TO_TIMESTAMP function can also have the below form.

SELECT TO_TIMESTAMP(
    '2022-03-31 4:35:20',
    'YYYY-MM-DD HH:MI:SS'
);

Output:

      to_timestamp      
------------------------
 2022-03-31 04:35:20+00
(1 row)

From the above example,

• YYYY: It is the four-digit year 2022
• MM: It is the month 03.
• DD: It is the day 31st.
• HH: It is hour 4.
• MI: It is minute 35.
• SS: It is the second 20.

Remarks with the PostgreSQL To_Timestamp Function

• FX prefix

The TO_TIMESTAMP() function skips the spaces in the input string till the (FX prefix) is used.

For example, the below code uses multiple spaces in the string. The TO_TIMESTAMP() function omits the spaces and returns the correct timestamp value.

SELECT 
    TO_TIMESTAMP('2021     Sep','FXYYYY MON');

Errors with the above example.

psql:commands.sql:2: ERROR:  invalid value "" for "MON"
DETAIL:  The given value did not match any of the allowed values for this field.

This is because the FX prefix tells the TO_TIMESTAMP() function to accept the input string with only one space.

Output after omitting the spaces.

SELECT 
    TO_TIMESTAMP('2021 Sep','FXYYYY MON');

Output:

      to_timestamp      
------------------------
 2021-09-01 00:00:00+00
(1 row)

• Milliseconds or microseconds are treated as seconds

While converting a string to a timestamp, the TO_TIMESTAMP() function treats the milliseconds or microseconds as seconds after the decimal point.

For example:

SELECT
	TO_TIMESTAMP('21-09-2022 12:2', 'DD-MM-YYYY SS:MS');

Output:

       to_timestamp       
--------------------------
 2022-09-21 00:00:12.2+00
(1 row)

From the above example, 2 is treated as 0.002 seconds, which means that

SELECT 
        TO_TIMESTAMP('21-09-2022 12:2', 'DD-MM-YYYY SS:MS');

and

SELECT
    	TO_TIMESTAMP('21-09-2022 12:200', 'DD-MM-YYYY SS:MS');

gives the same output.

To get 2 milliseconds, you can use the format  21-09-2022 12:002. 002; it is interpreted as 0.002 seconds, equivalent to 2 milliseconds.

• Year format

If the year in the string is less than 4 digits, the TO_TIMESTAMP() function can adjust it with the nearest year. For example, 97 becomes 1997,  and 17 becomes 2017.

For example:

SELECT
	TO_TIMESTAMP('04 22 99 12:45', 'MM DD YY HH:MI');

Output:

      to_timestamp      
------------------------
 1999-04-22 00:45:00+00
(1 row)

Conclusion

You learned about the PostgreSQL TO_TIMESTAMP with its syntax and examples in this article. PostgreSQL has more than 30 years of unique experience in developing a highly stable database management system. Due to its high integrity, reliability, and correctness levels, PostgreSQL has always been a primary data store or warehouse for many web, mobile, geospatial, and analytics applications.

If you are a PostgreSQL user, replicating data in a warehouse using ETL for data analysis can be demanding. This problem is exaggerated because they need extensive money and resources to hire data engineers and analysts to make sense of this data. 

Luckily, you can set up and start Data Replication from PostgreSQL to your favorite warehouse in a matter of minutes using Hevo. Hevo Data, with its strong integration with 150+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, allows you to not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

FAQ