Some things to note when using date types in PostgreSQL _ database other

It's not surprising when those of us who use rails see things like 5.weeks.from_nowor3.days.ago + 2.hours. Also, PostgreSQL can do this by simply calling PostgreSQL built-in functions to achieve the same functionality.
Current time/date/time stamp

There are a number of ways to get the current time, and before that we need to know the following two types of differences:

• Always returns the current value (Clock_timestamp ())
• Always returns the current value, but in a transaction it returns the time 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 the Clock_timestamp () is changed every time 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 these two function differences.

Time range: like 3 days ago

Using the interval operator you can easily build a time range, such as

• 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 do a simple math operation, which is especially good for building a time range like 3 days ago, such as:

postgres=# SELECT Now-interval ' 3 days ';
      ? Column?
-------------------------------
 2013-08-23 12:23:40.069717+02

Get a day of the week

Sometimes for a given time, you just want to know if it's the day of the week or what it belongs to in that century, or you just want to know that it's the first day of the year. 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
-----------
 
postgres=# SELECT Extract (DOW from-now ());
 Date_part
-----------
     1

Extract () has other, more powerful features, please refer to the official documentation for details, only a few of them are listed here:

• Day
• Century
• Dow (Day of week)
• Doy (Day of the year)
• Minute
• Month
• Year

Time zone Conversion

Sometimes, time zone conversions are especially useful for displaying different time zones at specific times. In time zone provides this functionality, how does it do that? We will demonstrate in a transaction because the now () function always returns the same value in the same transaction, so that we can easily see the differences that are displayed at the same time in different time zones.

postgres=# BEGIN;
BEGIN
postgres=# SELECT now ();
       Now
-------------------------------
 2013-08-26 12:39:39.122218+02
 
postgres=# SELECT now () in time ZONE ' GMT ';
     TimeZone
----------------------------
 2013-08-26 10:39:39.122218
 
postgres=# SELECT now () in time ZONE ' Gmt+1 ';
     TimeZone
----------------------------
 2013-08-26 09:39:39.122218
 
postgres=# SELECT now () in time ZONE ' PST ';
     TimeZone
----------------------------
 2013-08-26 02:39:39.122218