Shulou(Shulou.com)05/31 Report--

PostgreSQL how to use the date type, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Current time / date / time stamp

There are many ways to get the current time, and before that, we need to know the difference between the following two types:

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 postgres=# SELECT now (); now-- 2013-08-26 12 postgres=# SELECT now (); now-- 2013-08-26 12 postgres=# SELECT now () Clock_timestamp-- 2013-08-26 12 clock_timestamp-- 2013-08-26 12 clock_timestamp-- 51.123905 clock_timestamp-- () 50.698413 1902

You will find that the return value of clock_timestamp () changes each 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 the differences between the two functions.

Time range: for example, 3 days ago

You can easily build a time interval using the interval operator, 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 perform simple mathematical operations, which is especially suitable for building time intervals such as 3 days ago, such as:

Postgres=# SELECT now ()-interval'3 days';? column?-- 2013-08-23 12 purl 23purl 40.069717mm 02

Get what day it is

Sometimes for a given time, all you want to know is what day of the week it is 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, which are only a few listed here:

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 that? 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 differences in different time zones at the same time.

Postgres=# BEGIN;BEGINpostgres=# SELECT now (); now-- 2013-08-26 12 postgres=# SELECT now 39.122218 GMT'; timezone-- 02 postgres=# SELECT now () AT TIME ZONE 'GMT'; timezone-- 2013-08-26 10 Freight 39.122218 postgres=# SELECT now () AT TIME ZONE' GMT+1' Is it helpful for you to read the above contents after reading the above contents? timezone-- 2013-08-26 09 AT TIME ZONE 39.122218 postgres=# SELECT now () AT TIME ZONE 'PST'; timezone-- 2013-08-26 02RV 39.122218? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.