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

Source: Internet
Author: User
Tags time zones postgresql

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

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.