Those of us who use Rails will not be surprised when they see, for example, .from_nowor3.+. Similarly, PostgreSQL can do it, and you can implement the same functionality by simply calling PostgreSQL built-in functions.
Current time/date/time stamp
There are many ways to get the current time. Before this we need to know the following two types of differences:
- Always return the current value (clock_timestamp())
- Always return the current value, but in a transaction it returns the time when the transaction started (now())
Let's look at the following example
postgres=# BEGIN;
postgres=# SELECT now();
now
-------------------------------
2013-08-26 12:17:43.182331+02
postgres=# SELECT now();
now
-------------------------------
2013-08-26 12:17:43.182331+02
postgres=# SELECT clock_timestamp();
clock_timestamp
-------------------------------
2013-08-26 12:17:50.698413+02
postgres=# SELECT clock_timestamp();
clock_timestamp
-------------------------------
2013-08-26 12:17:51.123905+02
You will find that the return value of clock_timestamp() changes every time when the statement is executed, but now() always returns the same value. When you need to consider the time zone, you should pay special attention to the differences between the two functions.
Time interval: For example, 3 days ago
Using the interval operator you can easily build a time interval, for example
- interval '1 day'
- interval '5 days'
- interval '5 days' + interval '3 hours'
- interval '5 days 3 hours'
As you can see, we can use the interval operator to perform simple mathematical operations, which is particularly suitable for building time intervals such as 3 days ago, such as:
postgres=# SELECT now() - interval '3 days';
?column?
-------------------------------
2013-08-23 12:23:40.069717+02
Get the day of the week
Sometimes for a given time, what you just want to know is the day of the week or whether it belongs to that century or what day of the year it is. The extract() function in PostgreSQL provides this functionality.
The following example was tested on Monday, August 26.
postgres=# SELECT extract(DAY FROM now());
date_part
-----------
26
postgres=# SELECT extract(DOW FROM now());
date_part
-----------
1
Extract() has other more powerful features. Please refer to the official documentation for details. Here is a small list:
- day
- century
- dow(day of week)
- doy(day of year)
- minute
- month
- year
Time zone conversion
Sometimes time zone conversion is particularly useful for displaying specific times in different time zones. AT TIME ZONE provides this functionality, how does it do it? We will demonstrate in a transaction because the now() function always returns the same value in the same transaction, so we can easily see the difference displayed in different time zones at the same time.
postgres=# BEGIN;
BEGIN
postgres=# SELECT now();
now
-------------------------------
2013-08-26 12:39:39.122218+02
postgres=# SELECT now() AT TIME ZONE 'GMT';
timezone
----------------------------
2013-08-26 10:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'GMT+1';
timezone
----------------------------
2013-08-26 09:39:39.122218
postgres=# SELECT now() AT TIME ZONE 'PST';
timezone
----------------------------
2013-08-26 02:39:39.122218