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 sqlserverguidesNow 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);
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');
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;
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 Pattern | Pattern |
| To get a year in four digits | YYYY |
| To get the last three digits of a year | YYY |
| To get the last two digits of a year | YY |
| To show month name in capital letters | MONTH |
| To show month name with the first letter capitalized | Month |
| To show month name in lowercase letters | month |
| Month abbreviation in all capital, the first letter capitalized, and all lowercase letters, respectively | MON/Mon/mon |
| To show month number (1-12) | MM |
| Day name in all capital, first letter capitalized, and all lowercase letters, respectively | DAY/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 month | W |
| To show the week of the year | WW |
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’.
- 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 sqlserverguidesNow 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);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');
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;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.
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 sqlserverguidesNow 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);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');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;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'; 
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.
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 sqlserverguidesNow 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);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');
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;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 sqlserverguidesNow 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);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');
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;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 sqlserverguidesNow 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);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');
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');
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;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;
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;
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 sqlserverguidesNow 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);
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');
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;
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;
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;
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;
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 sqlserverguidesNow 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);
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');
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;
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;
Now we will again use SELECT statement in PostgreSQL to look for the difference:
SELECT Emp_ID,Name,Date_Of_Joining
From Employee;
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 sqlserverguidesNow 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);
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');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;
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;
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');
SELECT to_date('20-feb-2019', 'dd-mon-yyyy');
SELECT to_date('2021/06/21', 'yyyy/mm/dd');
This is an example of PostgreSQL date format convert.
You may also like the following PostgreSQL tutorials:
- PostgreSQL DATE_PART() Function with examples
- PostgreSQL TO_CHAR function
- Create a stored procedure in PostgreSQL
- PostgreSQL list databases
- PostgreSQL Data Types
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.