Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • PostgreSQL - Get Started
  • Install PostgreSQL
  • Connect to PostgreSQL DB
  • Create Database
  • Create Table
  • Copy Table
  • Drop Table
  • Drop Database
  • Truncate Table
  • ALTER Table
  • Rename Table
  • Rename Columns
  • Add Columns
  • Modify Column Type
  • Set Default Value of Column
  • Remove Columns
  • Add Constraints to Table
  • Insert Data
  • Upsert Data
  • Update Data
  • Delete Data
  • SELECT Statement
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • DISTINCT Clause
  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Natural Join
  • Cross Join
  • LIMIT OFFSET Clause
  • GROUPING SETS
  • GROUPING() Function
  • GROUP BY CUBE
  • GROUP BY ROLLUP
  • Sub Query
  • ALL Operator
  • ANY Operator
  • UNION Operator
  • INTERSECT Operator
  • EXCEPT Operator
  • IS NULL Operator
  • BETWEEN Operator
  • LIKE Operator
  • CAST Operator
  • CASE Expressions
  • NULLIF()
  • COALESCE()
  • GREATEST(), LEAST()
  • WITH Queries (CTE)
  • Constraints
  • NOT NULL Constraint
  • Unique Constraint
  • Check Constraint
  • Primary Key
  • Foreign Key
  • Sequence
  • Serial Type
  • Identity Columns
  • Generated Columns
  • Data Types
  • Boolean Type
  • Character Type
  • Integer Type
  • Numeric Type
  • Date Type
  • Time Type
  • TimeStamp Type
  • Interval Type
  • Array Type
  • Json Type
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

PostgreSQL: TIME Data Type

PostgreSQL supports TIME datatype to store the time values of a day.

The TIME datatype takes 8 bytes of storage. The TIME datatype can store up to 6 digits of precision to define a number of fractional digits placed in the second's field. The range for TIME datatype is from 00:00:00 to 24:00:00.

Syntax:
<column_name> TIME(<precision>)

The followings are different TIME format examples:

Example: TIME Formats
HH:MM		-> 07:10
HH:MM:SS	-> 07:10:34
HHMMSS	    -&gt; 071034

The TIME formats can be used with precision(p) as shown below:

Example: TIME Formats
MM:SS.pppppp		-> 11:34.666666
HH:MM:SS.pppppp	    -> 06:11:34.888888
HHMMSS.pppppp	    -&gt; 061134.888888

Let's create a table Work_Shift with a column of the TIME type, as shown below.

Example: TIME Type Column
CREATE TABLE Work_shift (  
    id serial PRIMARY KEY,
    shift_name VARCHAR NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

When you create a column with datatype as TIME, by default it will create a column as TIME without a time zone.

The following inserts data into a TIME column.

Example: Insert TIME Values
INSERT INTO Work_shift(shift_name, start_time, end_time)
VALUES
	('First Shift', '09:00:00', '06:00:00'),
	('Second Shift', '12:00:00', '21:00:00'),
	('Third Shift', '14:00:00', '23:00:00');

Let's check the data we inserted into Work_Shift table.

TIME Type with Time Zone

PostgreSQL allows us to define TIME with time zone type. The time zone can be specified as a full-time zone name like America/New_York or as time zone abbreviations like IST, PST etc.

The TIME with time zone takes 12 bytes of storage to store data. Some example are below.

Example:
06:55:34 IST
16:10:54 PST

PostgreSQL Time functions

Here are some Postgres provided Time functions.

CURRENT_TIME()

To get the current time with the time zone, use the CURRENT_TIME function. To get the current time with specific precision and with time zone use CURRENT_TIME(precision) function.

Example: CURRENT_TIME()
SELECT CURRENT_TIME, CURRENT_TIME(6);

LOCALTIME()

To get local time, use the LOCALTIME function. To get local time with specific precision use the LOCALTIME(precision) function.

Example: LOCALTIME()
SELECT LOCALTIME, LOCALTIME(5);

Extracting Hours, Minutes, Seconds from Time Value

To extract hours, minutes, and seconds from time value, use the EXTRACT functions as below.

Example: Get Hour, Minutes, Seconds
SELECT
    CURRENT_TIME,
    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;

Convert Time to Different Time Zones

Time can be converted to another time zone using the PostgreSQL functions.

The following converts a local time to the Eastern Standard Time (EST) using the following statement:

Example: Convert Value to Different Time Zone
SELECT LOCALTIME, CURRENT_TIME AT TIME ZONE 'EST';

Performing Arithmetic Operations on Time

PostgreSQL allows you to perform arithmetic operations like +, -, *, and / on time values and interval values.

In the following query result column adds 6 hours to localtime.

Example: Add Hours
SELECT LOCALTIME, LOCALTIME + interval '6 hours' AS result;

The following result column shows interval between LOCALTIME and TIME values.

Example: Get Interval
SELECT LOCALTIME, LOCALTIME - time '02:00' AS result;
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.