PostgreSQL To_Timestamp() Function: Syntax & Example Queries | A 101 Guide

on PostgreSQL • July 29th, 2022 • Write for Hevo

PostgreSQL To_Timestamp Featured Image

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 To_Timestamp in PostgreSQL. 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.

Table of Contents

Prerequisites

Understanding of database

What is PostgreSQL?

PostgreSQL logo
Image Source

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();

Scale your data integration effortlessly with Hevo’s Fault-Tolerant No Code Data Pipeline

As the ability of businesses to collect data explodes, data teams have a crucial role in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules. 

All of this, combined with transparent pricing and 24×7 support, makes us the most loved data pipeline software on review sites.

Take our 14-day free trial to experience a better way to manage data pipelines.

Get started for Free with Hevo!

PostgreSQL To_Timestamp in Overview

PostgreSQL To_Timestamp in  Overview
Image Source

Understanding PostgreSQL TO_TIMESTAMP

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.

PatternDescription
HHHour of Day (01-12)
HH12Hour of Day (01-12)
HH24Hour of Day (00-23)
YLast Digit of Year
YYLast Two Digits of the Year
YYYLast Three Digits of the Year
YYYYLast Four Digits of the Year
Y,YYYYear in Four Digits with Comma
MIMinute(00-59)
SSSecond(00-59)
MSMillisecond (000-999)
USMicrosecond (000000-999999)
SSSSSeconds 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)
IYYYISO Year (4 and more digits)
IYYLast Three Digits of ISO Year
IYLast Two Digits of ISO Year
ILast Digits of ISO Year
MONTHFull Uppercase Month Name
MonthFull Mixed-Case Month Name
monthFull Lowercase Month Name
monAbbreviated Lowercase Month Name
MMMonth 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 a unique experience of more than 30 years 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 webs, mobile, geospatial, and analytics applications.

Hopefully, you will try using the PostgreSQL TO_TIMESTAMP. Feel free to comment below expressing your thoughts or recommendations on PostgreSQL TO_TIMESTAMP.

If you are a PostgreSQL user, replicating data into a warehouse using ETL for Data Analysis can get 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 100+ 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. 

Visit our Website to Explore Hevo

Hevo lets you replicate your data from your PostgreSQL database to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Give Hevo a try. Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and decide on your best-suited plan. 

Share your thoughts on learning about PostgreSQL TO_TIMESTAMP in the comments below. If you have any questions, do let us know. We’d be happy to help.

No-code Data Pipeline For PostgreSQL