What is PostgreSQL timestamp | PostgreSQL timestamp examples

In my previous article I have given many examples of PostgreSQL. In this article I would like to give information about PostgreSQL timestamp with multiple real world industry examples. The PostgreSQL provides 2 types of datatype to handle timestamp. The first type is with the timezone and second type is without timezone. In this article I would like to provide you both timestamp information and PostgreSQL timestamp examples in detail.

There are two timestamp datatypes :

1.timestamp : This datatype does not have any timezone information

2.timestamptz: User can track the timezone information in timestamptz datatype.

PostgreSQL Timestamp datatype with PostgreSQL timestamp examples :

In this section I would like to explain the Timestamp datatype with multiple real life examples. I would like to give the syntax and how to use this timestamp datatype in detail.

1.Timestamp datatype allows you to store data as well as time.

2.Timestamp datatype does not allow you to store the timezone data.

3.Timestamp datatype is using 8 bytes to stored timestamp data.

Query to check the same is :

Select typename,typlen from pg_type

Where upper(typename)=’TIMESTAMP’;

Drawback :

When you change your database server timezone the timestamp value stored in database does not change. It is not recommended to use this datatype where you require timezone change or daylight saving change. It will improve maintenance work as database values are not changing.

Real Life example of Timestamp :

Step 1 : Create Table

Create a table with timestamp datatype and set its timezone as Indian Standard timezone.

Create table T_Timestamp_India(India_TS TIMESTAMP);

The above statement will create the table with column name India_TS.

Step 2 : Setting up Timezone

The next step is setting up the timezone. User needs to use set timezone command to set the correct timezone.

SET timezone = ‘India/Kolkatta’;

Step 3 : Insert values in to Timestamp

INSERT INTO T_Timestamp_India(India_TS)

VALUES   (    ‘2019-10-11 10:10:25-07’ );

Step 4 : Check the values.

Select * from T_Timestamp_India;

You will get output as,

2019-10-11 10:10:25

The timezone is not showing.

Timestamp with timezone datatype with Examples :

In this section I would like to explain the timestamp with timezone datatype.Timestamptz is used to store the timestamp with specified timezone.

1.Timestamptz datatype will store the date as well as time which is timezone aware datatype.

2.Postgresql stores timestamptz datatype in UTC format.

3.Timestamptz datatype is using 8 bytes to stored timestamp data.

It is very important to use timestamptz datatype rather than using only timestamp datatype. The time in database will automatically changed once server timezone is changed. I would like to explain this with example so that user will get importance of timestamptz datatype.

Real life example :

In following example I would like to show difference between timestamp and timestamptz datatypes.

Step 1 : Create Table with timestamp and timestamptz datatypes

Create a table with timestamp datatype and set its timezone as Indian Standard timezone.

Create table T_Timestamp_International(International_TS TIMESTAMP,International_TSTZ TIMESTAMPTZ);

The above statement will create the table with timestamp and timestamptz columns.

Step 2 : Setting up Timezone

The next step is setting up the timezone. User needs to use set timezone command to set the correct timezone.

SET timezone = ‘India/Kolkatta’;

Step 3:  Insert values in table

INSERT INTO  T_Timestamp_International(ts)

VALUES   (    ‘2019-10-11 10:10:25-07’ , ‘2019-10-11 10:10:25-07’);

Step 4 : Check and select for the values

Select * from T_Timestamp_International;

The above statement will retrieve 2 values.

International_TS	International_TSTZ
2019-10-11 10:10:25	2019-10-11 10:10:25-07′

Step 5: Set timezone to America Timezone

SET timezone = ‘America/New York’;

Step 6 : Check and select for the values after changing tz

Select * from T_Timestamp_International;

The above statement will retrieve 2 values.

International_TS	International_TSTZ
2019-10-11 10:10:25	2019-10-11 7:10:25-07′

After changing the timezone the values of timestamptz datatype has changed as per new York time but the value of timestamp datatype remain in Indian standard time.

These are two important datatype of dealing with PostgreSQL timestamp. When user wants to use PostgreSQL timestamp make sure that timezone requirement is there or not. Now a days different companies are opening their branches in different locations. So I suggest you to use timestamptz datatype in PostgreSQL timestamp datatype.I hope you like this article on PostgreSQL timestamp with examples. If you like this article or if you have any suggestions kindly comment in to comments section.