Time and date scripts commonly used in PostgreSQL use the tutorial
- 2020-05-06 11:52:16
- OfStack
gets the 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
time calculation
-- 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 may not be written, and its value may be
-- Abbreviation Meaning
-- Y Years
-- M Months (in the date part)
-- W Weeks
-- D Days
-- H Hours
-- M Minutes (in the time part)
Interception of time
-- extract extract(interval,timestamp);
select extract(year from now()); --2013
select extract(mon from now()); --5 in
time conversion
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 of and unix timestamps
SELECT TIMESTAMP 'epoch' + 1341174767 * INTERVAL '1 second';
--2012-07-01 20:32:47
instance
1. Current time/date/timestamp
There are many ways for to get the current time, and before we do, we need to know the difference between the following two types:
Always return 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'll notice that the return value of clock_timestamp() changes every time the statement is executed, but now() always returns the same value. When you need to consider time zones, you should pay special attention to these two function differences.
2. Time interval: e.g. 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 do simple math with the interval operator, which is especially useful for building time intervals 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 what day of the week it is or what century it belongs to 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
4. Time zone conversion
sometimes time zone conversions are particularly useful for displaying specific times in different time zones. AT TIME ZONE provides this functionality. How does it work? We will demonstrate this in a transaction, because the now() function always returns the same value in the same transaction, so we can easily see the difference displayed in different time zones at the same time.
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,
0