Time

In this section, we are going to understand the working of the PostgreSQL Time data type. And we also see examples of the Time data type, and we also see some accessible time functions, which help us to handle time values more efficiently.

What is PostgreSQL Time Data Type?

In PostgreSQL, the next data type is TIME, which stores the Time of days values.

The PostgreSQL Time Data type involves 8 bytes of storage and up to 6 digits of precision, and the range starts from 00:00:00 to 24:00:00 for Time data type.

Note: Here, the precision is used to define the number of fractional digits placed in the second field.

Syntax of PostgreSQL Time data type

PostgreSQL Time data type syntax is as follows:

column_name TIME(precision);  

Generally, we will use the following TIME formats, as shown below:

HH:MM     

HH:MM:SS  

HHMMSS 

Or If we want to use the precision, and then we will use the below Time formats:

MM:SS.pppppp      

HH:MM:SS.pppppp  

HHMMSS.pppppp 

Note: In the above format, the P is used for the precision value.

Let us see some examples of TIME value formats for our better understanding:

Firstly, we will see the sample example of without precision Time format value:

 06:05  

06:05:04  

060504 

Now, we will see the sample example of with precision Time format value:

06:59.999999  

06:05:04.333333  

060504.3333333 

Note: Usually, PostgreSQL takes almost any reasonable TIME format together with SQL-compatible, ISO 8601, etc.

Example of PostgreSQL TIME data type

Let us see one sample examples to understand how the PostgreSQL Time data type works.

We are creating one new table as Work_schedules with the CREATE command’s help and inserting some values using the INSERT command.

To create a Work_schedules into an Organization database, we use the CREATE command.

The Work_schedules table contains the various columns such as Comapny_id, Working_schedule, Login and Logout, and for the Login and Logout columns, we use the Time data type as shown in the following command:

CREATE TABLE Work_schedules (  

    company_id serial PRIMARY KEY,  

    Working_schedule VARCHAR NOT NULL,  

    Login TIME NOT NULL,  

    Logout TIME NOT NULL  

);    

Output

On executing the above command, we will get the following message: The Work_schedules table has been created successfully.

When the Work_schedules table is created successfully, we will insert some values into it with the INSERT command’s help.

INSERT INTO Work_schedules(Working_schedule,Login,Logout)  

VALUES  

('First shift', '09:00:00', '17:00:00'),  

 ('Second shift', '13:00:00', '21:00:00'),  

 ('Third shift', '16:00:00', '23:00:00'),  

 ('Weekend Shift', '09:00:00', '14:00:00');

Output

After implementing the above command, we will get the following message window, which displays that the particular values have been inserted successfully into the Work_schedules table.

After creating and inserting the Work_schedules table’s values, we will use the SELECT command to return all rows of the Work_schedules table:

SELECT *   

FROM Work_schedules; 

Output

After successfully implementing the above command, we will get the below output, which displays all the data present in the Work_schedules table:

PostgreSQL TIME with time zone type

PostgreSQL allows us the TIME with time zone data type along with the TIME data type, which permits us to deploy and retrieve the time of day through a Time zone.

To declare a column whose data type is TIME with time zone, we will use the following syntax:

column TIME with time zone  

The TIME with time zone data type contains 12 bytes, which allow us to store a time value with the time zone from 00:00:00+1459 to 24:00:00-1459 range.

The below example is used to display the TIME with time zone values:

    06:05:04 IST      

06:05:04.123-2    

Examples for PostgreSQL Time functions

We have the following Time functions such as CURRENT_TIME, CURRENT_TIME(precision) , LOCAL_TIME, LOCALTIME(precision), EXTRACT(), converting time value into a different time zone, and using arithmetic operator on time values available in the PostgreSQL, which help us to enhance the performance while we are using the ime data type.

Let us see different examples to understand how PostgreSQL time functions work.

• Getting the current time

We can use the CURRENT_TIME function to retrieve the current time with Time zone.

To return the current Time of the database server, we can use the below command:

SELECT CURRENT_TIME;  

Output

After implementing the above command, we will get the current time with the help of CURRENT_TIME function in the output, as shown below:

We can use the CURRENT_TIME(precision) function to retrieve the current time with particular precision.

Let us see a sample example for our better understanding.

The below command is used to get Current time with a specified precision:

SELECT CURRENT_TIME(4);  

Output

After executing the above command, we will get the following output, which shows the existing time with exact precision:

Note: As we can see in both the statements that if we define the CURRENT_TIME function without precision, then it will return a time value with the complete accessible precision.

• Getting a local time

We can use the LOCALTIME function to retrieve the local time, as shown in the below command:

SELECT LOCALTIME;  

Output

On implementing the above command, we will get the following output, which displays the local time:

In PostgreSQL, we have the LOCALTIME(precision) function, which is used to retrieve the local time with a particular precision like we find the current time precision with the help of the CURRENT_TIME(precision) function.

In the below example, we used the LOCALTIME(precision) function to get local time with specified precision.

SELECT LOCALTIME(1);  

Output

After implementing the above command, we will get the following output, which shows the local time with exact precision:

• Changing time to a different time zone

The below syntax is used to change the time to a different time zone:

[TIME with time zone] AT TIME ZONE time_zone  

Let see one sample example to understand in detail:

In the below example, we will try to change the local time to the time at the time zone PST (Pacific Standard Time) as shown in the following command:

SELECT LOCALTIME AT TIME ZONE 'PST';  

Output

After successfully executing of the above command, we will get the below output, which displays that the local time has been converted into the PST time zone.

• Extracting hours, minutes, seconds from a time value

We can use the EXTRACT function to extracting hours, minutes, seconds from a time value.

The below syntax is used to extract the hours, minutes, second for the PostgreSQL time data type:

EXTRACT(field FROM time_value);  

Note: In the above syntax, the field can be the hour, minute, second, milliseconds.

Let us see a sample example where we try to use the EXTRACT() function along with the CURRENT_TIME(precision) function into the SELECT command:

SELECT CURRENT_TIME(0),  

EXTRACT (HOUR FROM CURRENT_TIME) as Hour,  

EXTRACT (MINUTE FROM CURRENT_TIME) as Minute,   

EXTRACT (SECOND FROM CURRENT_TIME) as Second,  

EXTRACT (milliseconds FROM CURRENT_TIME) as Milliseconds; 

Output

After executing the above command, we will get the below output, which extracts the CURRENT_TIME(0) into Hours, Minute, Second, and Milliseconds:

• Arithmetic operations on time values

In PostgreSQL, we can use the arithmetic operators like +, -, and * on time values and among time and interval values.

In the below example, it is used to return an interval between two-time values:

SELECT time '09:00' - time '03:00' AS result;  

Output

We will get the below result on implementing the below command, which displays the time interval between 09:00- 03:00.

We will use the following command if we want to add 3 hours to the Local time:

SELECT LOCALTIME + interval '3 hours' AS Output;  

Output

After executing the above command, we will get the following result:

Note: In above example, the time value is the sum of a time value and an interval value.

Overview

In the PostgreSQL Time data type section, we have learned the following topics:

• The PostgreSQL Time data type is used to store the time values for a specified column.
• We used a different TIME functions, for example, CURRENT_TIME, CURRENT_TIME(precision), LOCAL_TIME, LOCAL_TIME(precision), EXTRACT(), to enhance and handle the TIME value from the particular table.
• We can convert the Time value into the different time zone.
• We used the Arithmetic operators on time value to find the time interval and sum of time value.