Working with dates in databases can be challenging as different databases will have different formats in which the dates are stored. In PostgreSQL, the date is formatted as yyyy-mm-dd. For example, for a September 15th, 2015 date, it would be formatted as 2015-09-15 in PostgreSQL. This is the same format that is used when we insert dates into a date column.
Note that we must quote the dates with single quotes like what we do with a string. For example, if we wanted to find all of the invoices that were submitted on January 1st, 2009, we would use the following query:
WHERE invoice_date = '2009-01-01';
Notice that with the invoice_date in the invoice table, the data type is of type timestamp. We’ll get into that in the next part but be aware that this does exist.
This is useful to find one date, but you can use ranges for date parameters as well. For example, if we wanted to find all invoices prior to January 31st, 2009, we can use the < operator in the comparison instead of the = operator.
WHERE invoice_date < '2009-01-31';
This will return the result set:
This method gives us more flexibility to control the date ranges.
There is also a datetime variable that stores both the date and time. The time is set as hh:mi:ss.mmm where mmm are in milliseconds.
There are some date-related functions that can be useful to use to get the current date and time. Note that these functions are based on the server date and time, so when you are running them, they may not be in your timezone. By default, on the PostgreSQL server that we use, the date and time are in GMT.
For example, you can use the following function in this query to get the current date and time:
When run, you should see a result similar to the following:
Notice that the results come in the format yyyy-mm-dd and then T for time and hh:mi:ss.mmm and ending with a Z.
If you only wanted to get the date and not the time, you can do a conversion using:
This would return
We can also just get the time by using:
We can change the format of the date using the TO_CHAR function. The TO_CHAR to convert the date takes in two parameters. The first parameter is the value that we want to format. The second is the template that defines the format. For example, if we wanted to output the current date in a mm/dd/yyyy format, we can do so by doing:
SELECT TO_CHAR(now()::date, 'mm/dd/yyyy');
There are many different template patterns for date formatting but some of the most common include:
Considering what you see above, think about what this command would return and run it in PostgreSQL:
SELECT TO_CHAR(now(), 'Day, Month dd, yyyy hh24:mi ss tz');