A Clear Summary and Examples of Dates, Times, and Timestamp Functions With PostgreSQL
If you did not enjoy handling dates and times before, I would recommend you going through my post and hopefully, you will change your mind. Let’s start.
If you would like to display the date, time with the timezone at the end, you can use either now() or CURRENT_TIMESTAMP as follows:
If you would like to display the date, time without the timezone, you can use now()::timestamp or LOCALTIMESTAMP as follows:
If you would like to display the the date, time with the timezone an hour from now and a day from now, you can do either one of the following:
Both queries will give you the same result:
If you would like to display the date, time without the timezone an hour from now and a day from now, you can do any of the following:
Both queries will give you the same result:
We can use TIMEOFDAY() to display all the following:
- The name of the day of the week( Sat, Sun, Mon, etc),
- The name of the month (Dec, Jan, Feb,etc),
- The day of the month (1to 31),
- The time (hour:min:sec:microsec),
- The year (yyyy) and
- The time zone abbreviation (e.g. EST, PST,etc).
To display only the current date, we can use CURRENT_DATE.
The date above is shown in the following format yyyy-mm-dd.
CURRENT_TIME can be used to display the current time with the timezone.
As we can see above the current time is shown in terms of hour:min:sec:microsec/Timezone.
We can add precision to the CURRENT_TIME CURRENT_TIME(precision). In the following example, I am selecting both the CURRENT_TIME without precision and the CURRENT_TIME with the precision set to 2.
To display the current time without the time zone, then we can use LOCALTIME instead of CURRENT_TIME.
Note:
- Any function that shows time that includes the word current will always show the timezone (CURRENT_TIME, CURRENT_TIMESTAMP).
- Any function that shows time that includes the word local will not show the timezone (LOCAL_TIME, LOCALTIMESTAMP).
If we would like to calculate someone’s age, we can use AGE(timestamp) function. By default AGE(timestamp) will subtract the timestamp provided from the CURRENT_DATE. We also can use AGE(timestamp1, timestamp2) function to subtract two timestamps from each other where timestamp1 is the CURRENT_DATE and timestamp2 is someone’s date of birth.
If we wish to extract a field from date or time, we can use either DATE_PART(Field, Source) or EXTRACT (Field FROM Source). Field can be sometning related to time(e.g. hour, minute, second, millisecond, microsecond) or related to the date (e.g. year, month , day). Note that when using DATE_PART(), the field has to be surrounded with single quotes. In the case of using EXTRACT(), it is optional to surround the field with single quotes (e.g. ‘hour’ or hour).
In the following queries, I will be selecting the year, month, day, century , day of the week(0–6) referred to as DOW, day of the year (1–365/366) referred to as DOY fields from the NOW() and CURRENT_DATE.
In the following queries, I will select the hour, minute, second from the CURRENT_TIME.
Let’s assume, we have an order table where customers submit orders for different products and we want to select the orders that were done at least a year ago taking into consideration that I am writing those queries in Dec 2020.
order (order_id, customer_id, order_date)
We can do the following:
Solution1:
Solution2:
Solution3:
All the above will give the following output:
To show the name of the day, you can do the following:
To show the name of the month, we can do the following:
Note: the different letter case of month and day, will affect the output (e.g. MON will result in the name of the month abbreviated and all upper case)
There is a function called DATE_TRUNC(date_part, field). It truncates a timestamp or interval to a specified level of precision.
For example:
SELECT DATE_TRUNC(‘hour’, LOCALTIME); will display 20:00:00, only the hour will be displayed with 00 in the rest of the time parts(hour:00:00).
SELECT DATE_TRUNC(‘minute’, LOCALTIME); will display the hour:minute:00, 20:22:00.
I would like to show you an example on when DATE_TRUNC() function could come handy.
Assume we will use the order table given previously and we would like to count the number of submissions performed per month for each year regardless of the day and time.
The DATE_TRUNC()(‘month’, order_date) will consider the day as 01 and if there was time given in the timestamp it will be turned into 00:00:00.
For converting a string to a date we can use the TO_DATE(string_date, format).
All of the above will give the following result:
The following function TO_TIMESTAMP(timestamp, format) can be used to convert a string to a timestamp based on a specified format.
All of the above will give the following result:
In the following query, I will select the customer_id and the last date the customer ordered something.
I want to figure out the number of days passed on the last date a customer made an order. So, I will subtract the MAX(order_date) which is the last date for an order and subtract it from the current date (CURRENT_DATE).
I want to create an extra field that will specify whether I should send the customer advertisements or not. Assume that I will send the customer advertisements if 90 days (3 months) have passed on his last order.
The (current_date — MAX(order_date)) will give string results (e.g. 116 days) so in order to be able to check if the result is > 90, I will use the EXTRACT() function to extract the days as an integer.
I hope you found the post interesting and that I was able to demonstrate the subject in a good and clear way.
