In this article, we will look into working with dates in PostgreSQL, including the various date data types and formats, their functions, and the ways how to deal with them in your everyday routine. We will be using dbForge Studio for PostgreSQL to illustrate the examples.
You can use dates and timestamps for data analysis and data storage, as they allow you to determine when an event actually occurred. The tricky thing about dates is that their formats vary in different countries, so it can be quite a challenging task for beginners in database management. The data type and format of the date column should always be consistent with the information entered by the user. In addition, you should modify the date format to meet the preferences of your users. Postgres is capable of handling a wide range of data formats.
Contents
- PostgreSQL DATE data type overview
- Using the current date as the default value
- Postgres DATE functions
- Using the NOW() function to get the current date
- Using the TO_CHAR() function – output the date value in a specified format
- Using the minus operator to get the interval between two dates
- Using the AGE() function to get the date difference in years, months, and days
- Using the EXTRACT() function to get the year, quarter, month, week, day from a date value
- Using TO_TIMESTAMP() to convert a string to a timestamp
- Summary
PostgreSQL DATE data type overview
DATE data type in PostgreSQL is used to store dates in the YYYY-MM-DD format (e.g. 2022-03-24). It needs 4 bytes to store a date value in a column. Note that the earliest possible date is 4713 BC and the latest possible date is 5874897 AD.
It is highly important to retain code readability at the stage of writing it. dbForge Studio for PostgreSQL can boast a convenient and intuitive PostgreSQL Formatter. Taking care of code formatting, syntax check, and code completion, the Postgres SQL formatter feature built-in dbForge Studio for PostgreSQL will let you stay focused on dealing with the DATE data type as well.
Using the current date as the default value
The default value in a DATE column in a Postgres database can be set by using the keyword DEFAULT CURRENT DATE, as in the script below:
CREATE TABLE myevents (
event_id serial PRIMARY KEY,
event_details VARCHAR (255) NOT NULL,
event_date DATE NOT NULL DEFAULT CURRENT_DATE
);
INSERT INTO myevents (event_details)
VALUES('Meeting the client ABC');
SELECT * FROM myevents;As you can see, a value for the [event date] column is automatically inserted by the PostgreSQL server, using the current system date in the format of YYYY-MM-DD.
Postgres DATE Functions
Sometimes, it is critical to alter the date format or calculate the values in the PostgreSQL table. We might need to do this for users from different time zones all over the world. In this case, SQL Functions are crucial.
Using the NOW() function to get the current date
SELECT now();You can use double colons (::) to cast a DATETIME value to a DATE value as shown below:
The current system timestamp is returned by the NOW() function. The syntax is as follows:
SELECT now()::date;
Using the TO_CHAR() function – output the date value in a specified format
To present the output of a Postgres date value in a specified format, the TO_CHAR() function comes in handy. The syntax is as follows:
TO_CHAR(expression, format)There are two parameters accepted by the TO_CHAR() function:
- Input date: The date to be converted into a specific format.
- Date format: The placeholder for a new date format specification.
Let’s see how to convert the existing date values from the [MyEvents] table into DD-MM-YYYY format.
SELECT event_details,event_date as ExistingDateformat,
to_char(event_date,'DD-MM-YYYY') As NewDateFormat FROM myevents;
To convert the current timestamp into the preferred format, you can fuse the TO_CHAR() and the NOW() functions.
SELECT TO_CHAR(NOW() :: DATE, 'dd-mm-yyyy');Using the minus operator to get the interval between two dates
To calculate the difference between two dates, use the minus (-) operator. The query below provides the time period between the present timestamp and [eventdate] from the MyEvents table.
SELECT event_date,now() as currentdate,
now()-event_date as Interval FROM myevents
where event_id=2;You may also select an interval for the date to be returned when a certain amount of time has passed. Have a look at the query below:
SELECT (NOW() + interval '3 hour') AS threehourslater,
(NOW() + interval '2 day') AS Twodaylater,
(NOW() - interval '2 year') AS TwoYearBefore,
(event_date- interval '1 year') as eventdatedifference
from myevents;
Using the AGE() function to get the date difference in years, months, and days
With the AGE() function, you can get the date difference in years, months, and days. This function can be used to determine a person’s age. The first date value is subtracted from the second date value with this function, which accepts two date inputs. When the values in the function script are reversed, the result is a negative value.
Let’s find out the age of the person whose birthdate is 1995-10-09. The syntax is as follows:
SELECT current_date,
AGE(timestamp '1995-10-09'::timestamp) as EmpAge;
Using the EXTRACT() function to get the year, quarter, month, week, day from a date value
The EXTRACT() function returns a day, week, month, year, and a quarter from the specified date value.
Extract a year:
SELECT EXTRACT(YEAR FROM TIMESTAMP '2022-04-09 10:30:15') as year;
Extract a month:
SELECT EXTRACT(Month FROM TIMESTAMP '2022-04-09 10:30:15') as Month;
Extract a quarter:
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2022-04-09 10:30:15') as QUARTER;
Extract the day of the week:
SELECT EXTRACT(DOW FROM TIMESTAMP '2022-04-09 10:30:15') as DOW;
Extract the day of the year:
SELECT EXTRACT(DOY FROM TIMESTAMP '2022-04-09 10:30:15') as DOY;
Note that dbForge Studio displays the query execution results each in a separate tab.
The EXTRACT() function can be easily combined with INTERVAL. Let’s assume we want to set the interval as 5 years 8 months 23 days 08 hours 10 minutes and 20 seconds. Individual values are returned by the EXTRACT function.
SELECT EXTRACT(YEAR
FROM INTERVAL '5 years 8 month 23 days 08 hours 10 minutes 20 seconds' ),
EXTRACT(Month
FROM INTERVAL '5 years 8 month 23 days 08 hours 10 minutes 20 seconds' ),
EXTRACT(Day
FROM INTERVAL '5 years 8 month 23 days 08 hours 10 minutes 20 seconds' ),
EXTRACT(hour
FROM INTERVAL '5 years 8 month 23 days 08 hours 10 minutes 20 seconds' ),
EXTRACT(Minute
FROM INTERVAL '5 years 8 month 23 days 08 hours 10 minutes 20 seconds' );
Using TO_TIMESTAMP() to convert a string to a timestamp
You can keep date values in UTC (Universal time coordinator) and change the time zone as needed if you would like to convert the timestamp into a different time zone.
SELECT * FROM pg_timezone_names;To check the current time zone, use the SHOW TIMEZONE command.
You can use AT TIME ZONE to receive the output for the chosen time zone by selecting the needed time zone value from the pg_timezone_names table.
SELECT '2022-04-09 10:36:19'::timestamp AT TIME ZONE 'America/Los_Angeles';
AT TIME ZONE allows you to have various time zone outputs.
SELECT '2022-04-09 10:36:19'::timestamp AT TIME ZONE 'America/Washington';
SELECT '2022-04-09 10:36:19'::timestamp AT TIME ZONE 'Asia/Beijing';
SELECT '2022-04-09 10:36:19'::timestamp AT TIME ZONE 'Europe/Kyiv';Summary
To sum up, the PostgreSQL DATE data type is very important and useful when storing dates and timestamps in tables, so it is quite common in a relational database. The range of their usage is incredibly wide, that’s why it is so important to make them clear for any user in any country and time zone. With various Postgres functions, it is easy to meet the users’ needs, as you can alter dates so they can be used in a specific time zone, with a certain format and other details that make working with such data much easier. Finally, if you are looking for a perfect combination of an intuitive graphical interface and an augmented command line that can make working with the DATE data type even more pleasant, dbForge Studio for PostgreSQL will come in handy.
