Time and date scripts used in PostgreSQL tutorial _postgresql

Source: Internet
Author: User
Tags current time time zones postgresql time and date

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

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 (); 
 2013-08-26 12:17:43.182331+02 
postgres=# SELECT now (); 
 2013-08-26 12:17:43.182331+02 
postgres=# SELECT clock_timestamp () ; 
 2013-08-26 12:17:50.698413+02 
postgres=# SELECT 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 ()); 
postgres=# SELECT Extract (DOW from-now ()); 

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; 
postgres=# SELECT now (); 
 2013-08-26 12:39:39.122218+02 
postgres=# SELECT now () in time ZONE ' GMT '; 
 2013-08-26 10:39:39.122218 
postgres=# SELECT now () in time ZONE ' Gmt+1 '; 
 2013-08-26 09:39:39.122218 
postgres=# SELECT now () in time ZONE ' PST '; 
 2013-08-26 02:39:39.122218 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.