Postgresql как изменить формат даты

In this guide, you’ll learn how to format DATE values into many different formats, how to insert DATE values into a table, and more.

Summary

You can insert date values into a DATE column in Postgres in many different formats, but the recommended format is the ISO format of YYYY-MM-DD.

You can format dates in Postgres when you display them using the TO_CHAR function, such as TO_CHAR(order_date, ‘MM-DD-YYYY’).

Postgres Date Data Type

We’ll be working with the DATE data type in this guide.

The DATE data type in Postgres captures the date without the time component.

There is a range of values it can store: from 4,713 BC to 5,874,897 AD.

To define a column with the DATE data type, simply specify the data type:

CREATE TABLE cust_order (
  order_id INT,
  order_date DATE
);

The order_date column is stored as a DATE value.

Let’s insert some data into this table.

You can find all of the SQL scripts used in this guide in my GitHub repository.

Insert a Date into a Table

In many other databases, you have to insert date values using a certain default format, or use a function to convert your input format into the required format.

However, in Postgres, you can insert date data in many different types, and it will work.

The recommended format is the one that meets the ISO 8601 standard, which is YYYY-MM-DD.

Here are a few examples of inserting data into a DATE column in Postgres.

INSERT INTO cust_order (order_id, order_date) VALUES (1, '2022-10-13');
INSERT INTO cust_order (order_id, order_date) VALUES (2, '14-OCT-2022');
INSERT INTO cust_order (order_id, order_date) VALUES (3, '20221015');

All three of these rows should be inserted without issues.

However, we can try to run this statement:

INSERT INTO cust_order (order_id, order_date) VALUES (4, '16-10-2022');

We’ll get an error:

ERROR:  date/time field value out of range: "16-10-2022"
LINE 1: ...INTO cust_order (order_id, order_date) VALUES (4, '16-10-202...
                                                             ^
HINT:  Perhaps you need a different "datestyle" setting.
SQL state: 22008
Character: 58

This happens because the format we have specified is not clear to the database which field is the day and which is the month. We know by looking at it that 16 is the day because there is no month number of 16.

However, the database doesn’t know this.

A way around this, as mentioned by the hint in the error message, is to use a different DateStyle setting. You can find out more in the Postgres documentation here.

We can select from this table and see the results.

SELECT order_id, order_date
FROM cust_order;

Results:

order_id	order_date
1	2022-10-13
2	2022-10-14
3	2022-10-15

The three rows that were inserted are shown here.

Display a Date in a Certain Format Using TO_CHAR

When we select a DATE column from our table, we can see the format that is shown:

SELECT order_id, order_date
FROM cust_order;

Results:

order_id	order_date
1	2022-10-13
2	2022-10-14
3	2022-10-15

The format of the date value is YYYY-MM-DD.

What if we want to show it in a different format?

You can use the TO_CHAR function in Postgres.

The TO_CHAR function looks like this:

TO_CHAR (date_value, output_format)

The parameters are:

• date_value: the value you want to display in a different format.
• output_format: the format to display

The output_format must be in single quotes and uses the patterns in the table below.

For example, a value in your function of YYYY will be replaced with the four-digit year when it is displayed.

This is the list of output format values that apply to dates.

Pattern	Description
Y,YYY	year (4 or more digits) with a comma
YYYY	year (4 or more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO 8601 week-numbering year
IYY	last 3 digits of ISO 8601 week-numbering year
IY	last 2 digits of ISO 8601 week-numbering year
I	last digit of ISO 8601 week-numbering year
BC, bc, AD, or ad	era indicator without periods
B.C., b.c., A.D. or a.d.	era indicator with periods
MONTH	full upper case month name (blank-padded to 9 characters)
Month	full capitalised month name (blank-padded to 9 characters)
month	full lower case month name (blank-padded to 9 characters)
MON	abbreviated upper case month name
Mon	abbreviated capitalised month name
mon	abbreviated lower case month name
MM	month number (01-12)
DAY	full upper case day name (blank-padded to 9 characters)
Day	full capitalised day name (blank-padded to 9 characters)
day	full lower case day name (blank-padded to 9 characters)
DY	abbreviated upper case day name (blank-padded to 9 characters)
Dy	abbreviated capitalised day name (blank-padded to 9 characters)
dy	abbreviated lower case day name (blank-padded to 9 characters)
DDD	day of year (001-366)
IDDD	day of ISO 8601 week-numbering year (001-371). Day 1 of the year is Monday of the first ISO week
DD	day of month (01-31)
D	day of the week, Sunday (1) to Saturday (7)
ID	ISO 8601 day of the week, Monday (1) to Sunday (7)
W	week of the month (1-5). The first week starts on the first day of the month
WW	week number of the year (1-53). The first week starts on the first day of the year
IW	week number of ISO 8601 week-numbering year (01-53). The first Thursday of the year is in week 1.
CC	2 digit century (the 21st century starts on 2001-01-01)
J	Julian Date (number of days since Nov 24, 4714 BC)
Q	quarter
RM	month in upper case Roman numerals (I-XII, I=January)
rm	month in lower case Roman numerals (i-xii, i=January)

Let’s see some examples.

Example 1 – DD/MM/YYYY

Here’s an example of formatting dates in the DD/MM/YYYY format.

SELECT
order_date, 
TO_CHAR(order_date, 'DD/MM/YYYY') AS formatted_date
FROM cust_order;

Results:

order_date	formatted_date
2022-10-13	13/10/2022
2022-10-14	14/10/2022
2022-10-15	15/10/2022

Example 2 – Names

Here’s an example using the names of days and months. We’ve also added two examples of the same part of the date: the Day (name of the day) and DD (day number).

SELECT
order_date, 
TO_CHAR(order_date, 'Day, DD Month, YYYY') AS formatted_date
FROM cust_order;

Results:

order_date	formatted_date
2022-10-13	Thursday, 13 October, 2022
2022-10-14	Friday, 14 October, 2022
2022-10-15	Saturday, 15 October, 2022

We can see the name of the day, the date, the month name, and the year.

Example 3 – Other Attributes

Here’s an example of getting other attributes of the date.

SELECT
order_date, 
TO_CHAR(order_date, 'MM') AS month_num,
TO_CHAR(order_date, 'DDD') AS day_of_year,
TO_CHAR(order_date, 'ID') AS day_of_week,
TO_CHAR(order_date, 'WW') AS week_of_year,
TO_CHAR(order_date, 'J') AS julian_date
FROM cust_order;

Results:

order_date	month _num	day_of _year	day_of _week	week_of _year	julian _date
2022-10-13	10	286	4	41	2459866
2022-10-14	10	287	5	41	2459867
2022-10-15	10	288	6	42	2459868

We can see a few different attributes of the date values here.

Frequently Asked Questions

How do I convert a date to a string in PostgreSQL?

You can use the TO_CHAR function to convert a date value to a string. The output of this function is a string.

As shown in the examples above, you can define the output format however you like.

How do I convert a datetime to a date in PostgreSQL?

To convert a DATETIME to a DATE (or remove the time component), you can either use the DATE function or the ::DATE operator.

You can enclose your DATETIME within the DATE function. Or, you can add ::DATE after the DATETIME value.

Here’s an example using the NOW() function, which returns the current date and time in a DATETIME format.

SELECT
NOW(),
NOW()::DATE,
DATE(NOW());

Results:

now	now	date
2022-10-13 18:28:10.356797+11	2022-10-13	2022-10-13

We can see in the second and third columns that the time component is removed.

Conclusion

Formatting a date in Postgres can be done using the TO_CHAR function which is quite flexible. You can also insert date values using a range of formats, but the recommended format is the ISO format of YYYY-MM-DD.

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

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);

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 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);

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 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);

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 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);

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 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);

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 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);

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 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);

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 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);

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 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);

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