Introduction to PostgreSQL Date Format
Dates are stored using the DATE datatype in PostgreSQL database. It takes 4 bytes of memory to store any date value in PostgreSQL. The range of DATE datatype is from 4713 BC to 5874897 AD. The format of the date in which it is stored and retrieved in PostgreSQL is yyyy-mm- dd. In this article, we will see how we can store, insert and retrieve the date values and manipulate them according to our requirements and convenience. We will also see the available functions that can help to change the format of the retrieved date and derive periodical parameters like age, day, month, year, and a week from a particular date value and getting the current date and assigning it as default values for columns that are of DATE datatype.
Syntax of PostgreSQL Date Format
DATE [DEFAULT CURRENT_DATE]
Let us create a table named educba containing date column in it.
CREATE TABLE educba (technical_id serial PRIMARY KEY,technology_name VARCHAR (255) NOT NULL,course_Duration INTEGER,starting_date DATE NOT NULL DEFAULT CURRENT_DATE,department VARCHAR(100));
Firing the above query in our psql terminal command prompt will result in the following output –
Let us insert the value in the educba table without mentioning the starting_date column’s value while inserting.
INSERT INTO educba(technology_name, course_duration, department) VALUES ('psql',35,'Database');
This gives the following output –
Let us now check the contents of our table educba by firing the following SELECT command – SELECT * FROM educba;
that gives the following output –
We can see that today’s date i.e 7th of April 2020 is inserted as the value in the starting_date column of our table even when we haven’t mentioned it while inserting the record. This is because we have mentioned the starting_date column of DATE datatype and having the default value of CURRENT_DATE that is the date at the movement when the record will be inserted according to the database server on which the value is inserted.
Functions of PostgreSQL Date Format
Now let us see some of the date-related functions that are available in PostgreSQL for date retrieval and manipulation.
We can either use the CURRENT_DATE variable and select its value of use Now() function to retrieve the current date and i,e and further to only retrieve date use Now():: date to retrieve current date value in PostgreSQL in the following way –
Now() function retrieves the full string of current date and time. If we fire the SELECT NOW(); command it will result in –
select now();
We can use:: double colons to retrieve only the date part from the above output along with date keyword in the following way –
SELECT NOW()::date;
that gives the following output –
or
SELECT CURRENT_DATE;
that results in the following –
Date Formats
We can get the dates in the format we want by converting the default format value of date present in yyyy-mm-dd to another format we wish to by using to_char function. The following is the syntax of TO_CHAR method –
TO_CHAR(datetobeConverted, targetTemplate);
As shown above, the TO_CHAR method accepts two parameters. The first parameter is the date that we want to convert to a certain format and the second parameter is the template of the target format in which we want the date value to be displayed.
Let us see some of the examples of TO_CHAR() method implementations –
The first template is today’s day and then after a comma the date and year to be displayed. For specifying today’s date, we will use the CURRENT_DATE variable. We can prepare our query statement for the same in the following way –
SELECT TO_CHAR(CURRENT_DATE, 'Day, dd yyyy');
Firing the above statement gives the following output –
Now, if I just want to display the day and date without month and year in it. Then my query statement will be –
SELECT TO_CHAR(CURRENT_DATE, 'FMDay, FMDD');
Firing the above statement gives the following output –
SELECT TO_CHAR(CURRENT_DATE, 'Day, DD');
SELECT TO_CHAR(CURRENT_DATE, 'mm/dd/yyyy');
Finding the difference between two dates
We can find the difference between two dates by simply subtracing one from the other using the minus operator (-). Let us consider an example,
SELECT CURRENT_DATE-'2020-04-01';
Firing the above query statement results in the following output –
There is a difference of 6 days between today’s date “2020-04-07” and “2020-04-01”.
Finding out the age
We can even find out the age of the person in terms of days, months, and years from the specified date until today if only one parameter is specified in the AGE() method. If two parameters are specified then the difference between both of them is given in days months and years format. Let us find out with the help of an example, how it works?
Consider a particular date, for example, “1996-01-26” is the birth date of someone. We have to find out how old is that person. Then we will use the following query statement to find the same.
SELECT AGE(DATE('1996-01-26'));
whose output is as follows –
So that person is 24 years 2 months and 12 days old.
Let us find how old was that person on 1st of June 2019 i.e ‘2019-06-01’. Then we can find out the result using the following query statement –
SELECT AGE('2019-06-01','1996-01-26');
So he/she was 23 years 4 months and 6 days old on the 1st of June 2019 if the birth date is 26th of January 1996. Note that the first parameter should always be the greater value that is a recent date and the second one should be older and smaller value. If not mentioned so then the result will be negative. Let us confirm the case by just swiping the first and second parameters –
SELECT AGE('1996-01-26', '2019-06-01');
whose output is as follows –
Conclusion
DATE datatype is used to store and manipulate the dates in PostgreSQL whose format is ‘yyyy-mm-dd’. However, by using TO_CHAR() method, we can change the format of the date while retrieval. Other than that, many other methods are available to manipulate date values such as minus operator, AGE(), MONTH(), YEAR() that can be used in PostgreSQL while dealing with dates.
Recommended Articles
This is a guide to PostgreSQL Date Format. Here we discuss the Functions and Syntax of PostgreSQL Date Format along with Finding out the age. You may also have a look at the following articles to learn more –
