Time and date scripts used in PostgreSQL tutorial _postgresql

Get system Time function

Select Now ();        --2013-11-28 16:20:25.259715+08
Select Current_timestamp;  --2013-11-28 16:20:38.815466+08
Select current_date;     --2013-11-28
Select Current_time;     --16:21:08.981171+08

Calculation of the time
--Use interval

Select Now () +interval ' 2 day '; --2013-11-30 16:21:47.610118+08 2 days later
select Now ()-interval ' 2 day ';--2013-11-26 16:22:03.390593+08 2 days ago
Select Now () +interval ' 2 hour '; --2013-11-28 18:22:14.578733+08 2 hours later

--Interval can not write, its value can be

--abbreviation   meaning- 
-Y Years-- 
M Months (in the date part)- 
-W          Weeks 
- -D           days 
--H           Hours- 
-M.        Minutes (in the time part)

The interception of Time
-Use Extract extract (Interval,timestamp);

Select Extract (Year from now ());    --2013
Select Extract (Mon from now ());     --May

Conversion of Time

Select timestamp ' 2012-05-12 18:54:54 ';         --2012-05-12 18:54:54
Select Date ' 2012-05-12 18:54:54 ';            --2012-05-12
Select time ' 2012-05-12 18:54:54 ';           --18:54:54
Select TIMESTAMP with time ZONE ' 2012-05-12 18:54:54 '--2012-05-12  18:54:54+08

Conversion to UNIX timestamp

 SELECT TIMESTAMP ' epoch ' + 1341174767 * INTERVAL ' 1 second '; 
--2012-07-01 20:32:47

Instance
1. 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.

2. Time range: For example 3 days ago
using the interval operator you can easily build a time range, such as

Interval ' 1 day '
interval ' 5 "
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 

3. Get the 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   

4. 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