PostgreSQL DATE Format + Examples

In this PostgreSQL tutorial, we will learn about PostgreSQL DATE Format with a few examples. We use the DATE data type in PostgreSQL to store DATE type values in a table. PostgreSQL supports many DATE Formats, and we will learn each one of them in detail. By the end of this tutorial, you will have complete knowledge of the PostgreSQL DATE Format.

  • PostgreSQL DATE Format
  • Postgresql date format functions
  • PostgreSQL DATE Format INSERT
  • PostgreSQL DATE Format WHERE Clause
  • PostgreSQL DATE Format yyyymmdd
  • PostgreSQL DATE Format mm/dd/yyyy
  • PostgreSQL DATE Format dd/mm/yyyy
  • PostgreSQL date format yyyy-mm
  • PostgreSQL date format month name
  • PostgreSQL DATE Format dd/mm/yyyy hh mm
  • PostgreSQL DATE Format With Timezone
  • PostgreSQL DATE Format 24 Hour
  • Postgresql date format yyyy-mm
  • Postgresql date format convert

PostgreSQL DATE Format

In this section, we will learn about PostgreSQL DATE Format. We use the DATE data type in PostgreSQL to store DATE type values in a table. PostgreSQL assigns 4 bytes of storage to a DATE value. The highest and the lowest value that can be stored in PostgreSQL DATE data type are 5874897 AD and 4713 BC.

PostgreSQL stores the DATE type values in YYYY-MM-DD format. The date can be inserted in many formats such as YYYY-MM-DD, YY-MM-DD, MM-DD-YYYY, etc. We will look at an example of how to define a DATE type column.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);
PostgreSQL DATE Format
PostgreSQL- Create Table DATE Format

Here we have created a column named Date_Of _Joining having DATE data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the YYYY-MM-DD format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');
PostgreSQL DATE Format example
PostgreSQL- INSERT DATA DATE Format

We INSERT the DATE inside single quotation marks as shown in the screenshot.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;
PostgreSQL DATE Format tutorial
PostgreSQL DATA Inserted Successfully DATE Format

In this section, we learned about PostgreSQL DATE Format.

Read How to create a table in PostgreSQL [Terminal + pgAdmin]

PostgreSQL date format functions

to_char( ): It is a function that helps in giving the output of a Postgres date value in a specified format. This function accepts the following two parameters.

  • Input date: It is the date that we want to convert into a specific format.
  • Date format: It is the way we specify the new date format.

Syntax: to_char(expression, format)

Template pattern supported by Date formatting:

Meaning of PatternPattern
To get a year in four digitsYYYY
To get the last three digits of a yearYYY
To get the last two digits of a yearYY
To show month name in capital lettersMONTH
To show month name with the first letter capitalizedMonth
To show month name in lowercase lettersmonth
Month abbreviation in all capital, the first letter capitalized, and all lowercase letters, respectivelyMON/Mon/mon
To show month number (1-12)MM
Day name in all capital, first letter capitalized, and all lowercase letters, respectivelyDAY/Day/day
To get the day of the year (001 to 366)DDD
To get the day of the month (01 to 31)DD
To get the day of the week (Sunday (1) to Saturday (7))D
To get a week of the monthW
To show the week of the yearWW

to_date( ): It is a function that can be used to convert string to date and its return type is the date.

syntax: to_date(text,text)

SELECT to_date('04 Jan 2021', 'DD Mon YYYY');

From the above code, we are converting string ’04 Jan 2021′ to date ‘2020-01-04’.

Postgresql date format functions
Postgresql date format functions
  • now( ): It is used to get the current dates with timezone.
  • typecast (::): It is an operator that we use to cast from one data type to another. Here we will use for casting DateTime value to date value.

PostgreSQL DATE Format INSERT

In this section, we will learn about PostgreSQL DATE Format INSERT, i.e., how to insert DATE type values in a PostgreSQL table. There are many DATE formats that can be used to insert data in a PostgreSQL table and we will learn about some of the DATE formats in the subsequent sections.

In this section, we will look at an example of the Month DD, YYYY format.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);
PostgreSQL DATE Format INSERT
PostgreSQL- Create Table DATE Format

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the Month DD, YYYY format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'May 25, 2016');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'August 12, 2018');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'October 21, 2019');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'June 10, 2017');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'September 16, 2018');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'December 07, 2018');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'July 13, 2017');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'March 05, 2018');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'November 24, 2020');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'January 23, 2016');
PostgreSQL Insert DATA DATE MDY
PostgreSQL- Insert DATA DATE MDY Format

We have inserted the DATE type values in Month DD, YYYY format inside single quotation marks.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT * 
FROM Employee;
PostgreSQL DATE Format INSERT to table
PostgreSQL DATA Inserted Successfully DATE Format

It is clear from the screenshot that PostgreSQL stores the DATE type in YYYY-MM-DD format even if we insert it in any format.

In this section, we learned about PostgreSQL DATE Format INSERT.

Another Example:

In Postgresql, we can insert the Date in a different format in a table, let’s understand through an example.

Create a table named date_format.

CREATE TABLE date_format(new_date date);

In the above code, we are creating a table named date_format with column new_date that is going to contains the date in a different format.

Insert the following records.

INSERT INTO date_format(new_date)VALUES(to_date('2021 Jan 10','YYYY Mon DD'));

INSERT INTO date_format(new_date)VALUES(to_date('2021/01/10','YYYY/MM/DD'));

INSERT INTO date_format(new_date)VALUES(to_date('2021-01-10','YYYY-MM-DD'));

from the above code, we are adding data in different formats, but Postgresql will store in format YYYY-MM-DD.

The output of the above code is given below.

Postgresql date format insert
Postgresql date format insert

This is an example of a PostgreSQL date format insert.

Read PostgreSQL installation on Linux step by step

PostgreSQL DATE Format WHERE Clause

In this section, we will learn about PostgreSQL DATE Format WHERE Clause. We can use the DATE type column with WHERE clause in PostgreSQL to filter data according to a condition. We will look at an example where we will filter data using the WHERE clause in PostgreSQL having a DATE data type.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);
PostgreSQL DATE Format WHERE Clause
PostgreSQL- Create Table DATE Format

Here we have created a column named Date_Of _Joining having DATE data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the YYYY-MM-DD format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'2016-05-25');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'2018-08-12');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'2019-10-21');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'2017-06-10');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'2018-9-16');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'2018-12-07');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'2017-07-13');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'2018-03-05');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'2020-11-24');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'2016-01-23');
PostgreSQL INSERT DATA DATE
PostgreSQL- INSERT DATA DATE Format

We INSERT the DATE inside single quotation marks as shown in the screenshot.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;
PostgreSQL DATE Format WHERE Clause example
PostgreSQL- DATA Inserted Successfully DATE Format

Now we will use the WHERE clause in SELECT statement to filter data according to DATE date type:

SELECT *
FROM Employee
WHERE Date_Of_Joining > '2018-01-01'; 
PostgreSQL WHERE DATE
PostgreSQL- WHERE Clause DATE Format

In this example, we have filtered out the data of those employees who have joined the company after ‘ 2018-01-01 ‘.

In this section, we learned about PostgreSQL DATE Format WHERE clause.

Another Example:

In Postgresql, we can format the date using the WHERE clause for a certain range of dates.

Let’s run the below code to format the date in pattern YYYY/MM/DD.

SELECT product_id,to_char(expiry_date,'YYY/MM/DD') FROM date_days
WHERE product_id BETWEEN 2 AND 5;

In the above code, we are showing the date in different formats like YYYY/MM/DD in the SELECT statement for product_id from 2 to 5 using the WHERE clause.

The output of the above code is given below.

Postgresql date format where clause
Postgresql date format where clause

This is an example of Postgresql date format where clause.

Read How to connect to PostgreSQL database

PostgreSQL DATE Format yyyymmdd

In this section, we will learn about PostgreSQL DATE Format YYYYMMDD. We know that we can insert DATE in various formats such as YYYY-MM-DD, YY-MM-DD, MM-DD-YYYY, YYYYMMDD, etc. So we will look at an example where we will use the YYYYMMDD format to insert a DATE type value in PostgreSQL.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);
PostgreSQL DATE Format yyyymmdd
PostgreSQL- Create Table DATE Format

Here we have created a column named Date_Of _Joining having DATE data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the YYYYMMDD format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'20160525');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'20180812');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'20191021');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'20170610');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'20180916');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'20181207');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'20170713');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'20180305');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'20201124');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'20160123');
PostgreSQL DATE Format yyyymmdd example
PostgreSQL Insert DATA DATE YMD Format

We have inserted the DATE type values in YYYYMMDD format inside single quotation marks.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;
PostgreSQL DATE Format yyyymmdd
PostgreSQL DATA Inserted Successfully DATE Format

It is clear from the screenshot that PostgreSQL stores the DATE type values in YYYY-MM-DD format irrespective of any format used while inserting the same, in this case, we used YYYYMMDD format to insert DATE type values and PostgreSQL stored it in YYYY-MM-DD format.

In this section, we learned about PostgreSQL DATE Format YYYYMMDD.

Read How to Uninstall PostgreSQL (Linux, Mac, and Windows)

PostgreSQL DATE Format mm/dd/yyyy

In this section, we will learn about PostgreSQL DATE Format MM/DD/YYYY. As we know that DATE type values can be inserted in many formats such as YYYY-MM-DD, YY-MM-DD, MM-DD-YYYY, MM/DD/YYYY, etc. So we will look at an example of PostgreSQL DATE MM/DD/YYYY format.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);
PostgreSQL DATE Format mm/dd/yyyy
PostgreSQL- Create Table DATE Format

Here we have created a column named Date_Of _Joining having DATE data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the MM/DD/YYYY format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'05/25/2016');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'08/12/2018');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'10/21/2019');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'06/10/2017');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'09/16/2018');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'12/07/2018');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'07/13/2017');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'03/05/2018');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'11/24/2020');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'01/23/2016');
PostgreSQL DATE Format mm/dd/yyyy example
PostgreSQL- Insert Data MDY DATE Format

We have inserted the DATE type values in YYYYMMDD format inside single quotation marks.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;
DATE Format mm/dd/yyyy in PostgreSQL
PostgreSQL DATA Inserted Successfully DATE Format

It is clear from the screenshot that PostgreSQL stores the DATE type values in YYYY-MM-DD format irrespective of any format used while inserting the same, in this case, we used MM/DD/YYYY format to insert DATE type values and PostgreSQL stored it in YYYY-MM-DD format.

In this section, we learned about PostgreSQL DATE Format MM/DD/YYYY.

Read How to Restart PostgreSQL

PostgreSQL DATE Format dd/mm/yyyy

In this section, we will learn about PostgreSQL DATE Format DD/MM/YYYY. As we know that DATE type values can be inserted in many formats such as YYYY-MM-DD, YY-MM-DD, MM-DD-YYYY, etc.

PostgreSQL does not support DD/MM/YYYY input format for the DATE data type. There is an alternative format DD-Month-YYYY where we can specify the DATE type values in DMY format, eg., 23-Dec-2016 So we will look at an example of PostgreSQL DATE DD-Month-YYYY format.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining DATE);
PostgreSQL DATE Format dd/mm/yyyy
PostgreSQL- Create Table DATE Format

Here we have created a column named Date_Of _Joining having DATE data type.

If we try to insert data in DD/MM/YYYY format, then we get an error message:

INSERT INTO Employee VALUES(1,'James','IT',3500,'25/05/2016');
PostgreSQL DATE Format dd/mm/yyyy example
PostgreSQL- Error Message DATE Format

So now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the DD-Month-YYYY format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'25-May-2016');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'12-Aug-2018');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'21-Oct-2019');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'10-Jun-2017');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'16-Sep-2018');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'07-Dec-2018');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'13-Jul-2017');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'05-Mar-2018');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'24-Nov-2020');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'23-Jan-2016');
PostgreSQL DATE Format dd/mm/yyyy
PostgreSQL- Insert DATA DATE DMY Format

We have inserted the DATE type values in DD-MON-YYYY format inside single quotation marks.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;
DATE Format dd/mm/yyyy in PostgreSQL
PostgreSQL DATA Inserted Successfully DATE Format

It is clear from the screenshot that PostgreSQL stores the DATE type values in YYYY-MM-DD format irrespective of any format used while inserting the same, in this case, we used DD-MON-YYYY format to insert DATE type values and PostgreSQL stored it in YYYY-MM-DD format.

In this section, we learned about PostgreSQL DATE Format DD/MM/YYYY or DD-MON-YYYY.

Here is another simple example:

In Postgresql, to show the date in a format like dd/mm/yyyy, here we will use the to_char function.

Use the below code.

SELECT to_char(now()::date,'dd/mm/yyyy') as currentdate;
Postgresql date format dd/mm/yyyy
Postgresql date format dd/mm/yyyy

In the above output, we have formatted the date value specified format dd/mm/yyyy in the “to_char(now():: date,’ dd/mm/yyyy)” function, where the first datetime cast into date value using typecast:: date operator with the name of the data type to cast, then formatted in dd/mm/yyyy format.

This is an example of PostgreSQL date format dd/mm/yyyy.

Read Postgresql create user with password and PostgreSQL WHERE with examples

PostgreSQL date format yyyy-mm

In Postgresql, To format the date in yyyy-mm format only which means we will only show the year and month.

Let’s run the below code to format the date in yyyy-mm.

SELECT to_char(now()::date,'yyyy-mm') as year_month;
Postgresql date format yyyy-mm
Postgresql date format yyyy-mm

This is an example of PostgreSQL date format yyyy-mm.

PostgreSQL DATE Format dd/mm/yyyy hh mm

In this section, we will learn about PostgreSQL DATE Format DD/MM/YYYY HH12:MI. PostgreSQL stores the DATE type values in YYYY-MM-DD format. So if we wish to INSERT DATE type values in DD/MM/YYYY HH MM format then we have to use TIMESTAMP data type. As it stores the preceding time as well.

We will look at an example where we will insert in DD-MON-YYYY HH:MI format and will use the TO_CHAR() to produce the SELECT query output in DD/MM/YYYY HH12:MI format.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining TIMESTAMP);
PostgreSQL DATE Format dd/mm/yyyy hh mm
PostgreSQL Create Table TIMESTAMP

Here we have created a column named Date_Of _Joining having TIMESTAMP data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the DD-MON-YYYY HH:MM format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'25-May-2016 10:30');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'12-Aug-2018 11:00');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'21-Oct-2019 11:30');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'10-Jun-2017 10:00');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'16-Sep-2018 09:30');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'07-Dec-2018 10:15');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'13-Jul-2017 10:45');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'05-Mar-2018 11:15');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'24-Nov-2020 11:45');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'23-Jan-2016 10:30');
PostgreSQL DATE Format dd/mm/yyyy hh mm
PostgreSQL- Insert DATA TIMESTAMP

We have inserted the DATE type values in DD-MON-YYYY HH:MM format inside single quotation marks.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;
DATE Format dd/mm/yyyy hh mm in PostgreSQL
PostgreSQL- DATA Inserted Successfully TIMESTAMP

Here we can see that the data has been inserted successfully and the Date_Of_Joining stores the TIMESTAMP in YYYY-MM-DD HH:MM:SS format.

Now we will use the TO_CHAR() function in SELECT statement to get the TIMESTAMP in DD/MM/YYYY HH12:MI format:

SELECT Emp_ID,Name,TO_CHAR(Date_Of_Joining, 'MM/DD/YYYY HH12:MI')
FROM Employee;
PostgreSQL SELECT Statement TIMESTAMP
PostgreSQL- SELECT Statement TIMESTAMP

We have specified the DD/MM/YYYY HH:MI format in the TO_CHAR() function inside the PostgreSQL SELECT statement.

In this section, we learned about PostgreSQL DATE Format dd/mm/yyyy hh mm or DD/MM/YYYY HH12:MI.

Another example:

In Postgresql, To format date with time like dd/mm/yyyy hh mm where dd for a date, mm for a month, yyyy for a year, hh for an hour, mm for a month number.

Use the below statement to show the date in dd/mm/yyyy hh mm.

SELECT to_char(now(),'dd/mm/yyyy hh mm') as date_time;
Postgresql date format dd/mm/yyyy hh mm
Postgresql date format dd/mm/yyyy hh mm

This is an example of PostgreSQL date format dd/mm/yyyy hh mm.

Read PostgreSQL CASE with Examples

PostgreSQL date format month name

In Postgresql, We can show the date with the month name by providing the pattern name Month to the “to_char( )” function.

Use the below syntax.

SELECT to_char(now()::date, 'dd month yyyy') as date_time;
Postgresql date format month name
Postgresql date format month name

This is an example of PostgreSQL date format month name.

PostgreSQL DATE Format With Timezone

In this section, we will learn about PostgreSQL DATE format With Timezone. PostgreSQL stores the DATE type values in YYYY-MM-DD format. So if we wish to INSERT DATE type values with Timezone then we have to use TIMESTAMPTZ data type, as it changes the timezone of the data using the SET command.

So we will look at an example where we will switch between two timezones and note the difference.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining TIMESTAMPTZ);
PostgreSQL Create Table TIMESTAMPTZ
PostgreSQL- Create Table TIMESTAMPTZ

Here we have created a column named Date_Of _Joining having TIMESTAMPTZ data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the DD-MON-YYYY HH:MM format to INSERT the Date_Of_Joining:

INSERT INTO Employee VALUES(1,'James','IT',3500,'25-May-2016 13:30');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'12-Aug-2018 14:00');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'21-Oct-2019 14:30');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'10-Jun-2017 13:00');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'16-Sep-2018 12:30');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'07-Dec-2018 13:15');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'13-Jul-2017 13:45');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'05-Mar-2018 14:15');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'24-Nov-2020 14:45');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'23-Jan-2016 13:30');
PostgreSQL Insert Data TIMESTAMP 24 HOUR
PostgreSQL- Insert Data TIMESTAMP 24 HOUR

We have inserted the DATE type values in DD-MON-YYYY HH24:MM format inside single quotation marks.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;
PostgreSQL DATE Format With Timezone
PostgreSQL- DATA Inserted Successfully TIMESTAMPTZ

Now we will set the TIMEZONE to ‘America/New_York’ using the SET command and note the difference:

SET TIMEZONE = 'America/New_York';

We can check the current TIMEZONE using the SHOW command:

SHOW TIMEZONE;
PostgreSQL SET TIMEZONE example
PostgreSQL- SET TIMEZONE

Now we will again use SELECT statement in PostgreSQL to look for the difference:

SELECT Emp_ID,Name,Date_Of_Joining 
From Employee;
PostgreSQL TIMEZONE America
PostgreSQL- TIMEZONE Set to America/New_York

It is clear from the screenshot that the TIMEZONE for the TIMESTAMP type Date_Of_Joining has been changed to ‘America/New_York’ as there is a clear difference of 11 Hours in the Date_Of_Joining Field.

In this section, we learned about PostgreSQL DATE Format With Timezone.

Read PostgreSQL WHERE IN with examples

PostgreSQL DATE Format 24 Hour

In this section, we will learn about PostgreSQL DATE Format 24 Hour. PostgreSQL stores the DATE type values in YYYY-MM-DD format. So if we wish to INSERT DATE type values in 24HOUR format then we have to use TIMESTAMP data type. As it stores the preceding time as well.

We will look at an example where we will insert in DD-MON-YYYY HH:MI format and will use the TO_CHAR() to produce the SELECT query output in DD/MM/YYYY HH24:MI format.

First, connect to the database in PostgreSQL using the \c command and the name of the database:

\c sqlserverguides

Now create a table in PostgreSQL using these lines of code having a column with DATE data type:

CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Salary money,
Date_Of_Joining TIMESTAMP);
PostgreSQL DATE Format 24 Hour
PostgreSQL Create Table TIMESTAMP

Here we have created a column named Date_Of _Joining having TIMESTAMP data type.

Now we will insert some data in this table using the PostgreSQL INSERT statement, and we will use the DD-MON-YYYY HH:MM format to INSERT the Date_Of_Joining:


INSERT INTO Employee VALUES(1,'James','IT',3500,'25-May-2016 13:30');
INSERT INTO Employee VALUES(2,'Simona','Electrical',2500,'12-Aug-2018 14:00');
INSERT INTO Employee VALUES(3,'Kiley','Mechanical',2250,'21-Oct-2019 14:30');
INSERT INTO Employee VALUES(4,'Fletcher','IT',3000,'10-Jun-2017 13:00');
INSERT INTO Employee VALUES(5,'Alisha','IT',2700,'16-Sep-2018 12:30');
INSERT INTO Employee VALUES(6,'Chanel','Mechanical',2500,'07-Dec-2018 13:15');
INSERT INTO Employee VALUES(7,'Erick','IT',3250,'13-Jul-2017 13:45');
INSERT INTO Employee VALUES(8,'Amber','Electrical',2700,'05-Mar-2018 14:15');
INSERT INTO Employee VALUES(9,'Stephen','Electrical',2000,'24-Nov-2020 14:45');
INSERT INTO Employee VALUES(10,'Jose','HR',3700,'23-Jan-2016 13:30');
PostgreSQL DATE Format 24 Hour example
PostgreSQL- Insert Data TIMESTAMP 24 HOUR

We have inserted the DATE type values in DD-MON-YYYY HH24:MM format inside single quotation marks.

We have successfully entered the data which can be checked by using the SELECT statement in PostgreSQL:

SELECT *
FROM Employee;
DATE Format 24 Hour in PostgreSQL
PostgreSQL- DATA Inserted Successfully TIMESTAMP 24HOUR

Here we can see that the data has been inserted successfully and the Date_Of_Joining stores the TIMESTAMP in YYYY-MM-DD HH24:MM:SS format.

Now we will use the TO_CHAR() function in SELECT statement to get the TIMESTAMP in DD/MM/YYYY HH12:MI format:

SELECT Emp_ID,Name,TO_CHAR(Date_Of_Joining, 'MM/DD/YYYY HH24:MI')
FROM Employee;
PostgreSQL 24HOUR TIMESTAMP
PostgreSQL- 24HOUR FORMAT TIMESTAMP

We have specified the DD/MM/YYYY HH24:MI format in the TO_CHAR() function inside the PostgreSQL SELECT statement to display the TIMESTAMP in 24 HOUR Format.

Another Example:

In Postgresql, we can convert the string to date with a specific format, syntax is given below.

to_date(string, format)

string: It is a string that we will convert into a date.

format: It is a pattern or format of a date.

Let’s understand through different examples with different formats.

SELECT to_date('20210325','yyyymmdd');
Postgresql date format convert
Postgresql date format convert
SELECT to_date('20-feb-2019', 'dd-mon-yyyy');
Postgresql date format convert
Postgresql date format convert
SELECT to_date('2021/06/21', 'yyyy/mm/dd');
Postgresql date format convert
Postgresql date format convert

This is an example of PostgreSQL date format convert.

You may also like the following PostgreSQL tutorials:

In this tutorial, we learned, about PostgreSQL DATE Format. Also, we have covered these topics:

  • PostgreSQL DATE Format
  • PostgreSQL DATE Format INSERT
  • PostgreSQL DATE Format WHERE Clause
  • PostgreSQL DATE Format yyyymmdd
  • PostgreSQL DATE Format mm/dd/yyyy
  • PostgreSQL DATE Format dd/mm/yyyy
  • PostgreSQL date format yyyy-mm
  • PostgreSQL date format month name
  • PostgreSQL DATE Format dd/mm/yyyy hh mm
  • PostgreSQL DATE Format With Timezone
  • PostgreSQL DATE Format 24 Hour

Leave a Comment