9.9 日期時間函式及運算子
Table 9.31 shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9.30 illustrates the behaviors of the basic arithmetic operators (+
, *
, etc.). For formatting functions, refer to Section 9.8. You should be familiar with the background information on date/time data types from Section 8.5.
All the functions and operators described below that take time
or timestamp
inputs actually come in two variants: one that takes time with time zone
or timestamp with time zone
, and one that takes time without time zone
or timestamp without time zone
. For brevity, these variants are not shown separately. Also, the +
and *
operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair.
Table 9.30. Date/Time Operators
+
date '2001-09-28' + integer '7'
date '2001-10-05'
+
date '2001-09-28' + interval '1 hour'
timestamp '2001-09-28 01:00:00'
+
date '2001-09-28' + time '03:00'
timestamp '2001-09-28 03:00:00'
+
interval '1 day' + interval '1 hour'
interval '1 day 01:00:00'
+
timestamp '2001-09-28 01:00' + interval '23 hours'
timestamp '2001-09-29 00:00:00'
+
time '01:00' + interval '3 hours'
time '04:00:00'
-
- interval '23 hours'
interval '-23:00:00'
-
date '2001-10-01' - date '2001-09-28'
integer '3'
(days)
-
date '2001-10-01' - integer '7'
date '2001-09-24'
-
date '2001-09-28' - interval '1 hour'
timestamp '2001-09-27 23:00:00'
-
time '05:00' - time '03:00'
interval '02:00:00'
-
time '05:00' - interval '2 hours'
time '03:00:00'
-
timestamp '2001-09-28 23:00' - interval '23 hours'
timestamp '2001-09-28 00:00:00'
-
interval '1 day' - interval '1 hour'
interval '1 day -01:00:00'
-
timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'
interval '1 day 15:00:00'
*
900 * interval '1 second'
interval '00:15:00'
*
21 * interval '1 day'
interval '21 days'
*
double precision '3.5' * interval '1 hour'
interval '03:30:00'
/
interval '1 hour' / double precision '1.5'
interval '00:40:00'
Table 9.31. Date/Time Functions
age(timestamp
, timestamp
)
interval
Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days
age(timestamp '2001-04-10', timestamp '1957-06-13')
43 years 9 mons 27 days
age(timestamp
)
interval
Subtract from current_date
(at midnight)
age(timestamp '1957-06-13')
43 years 8 mons 3 days
clock_timestamp()
timestamp with time zone
Current date and time (changes during statement execution); see Section 9.9.4
current_timestamp
timestamp with time zone
Current date and time (start of current transaction); see Section 9.9.4
date_part(text
, timestamp
)
double precision
Get subfield (equivalent to extract
); see Section 9.9.1
date_part('hour', timestamp '2001-02-16 20:38:40')
20
date_part(text
, interval
)
double precision
Get subfield (equivalent to extract
); see Section 9.9.1
date_part('month', interval '2 years 3 months')
3
date_trunc(text
, timestamp
)
timestamp
Truncate to specified precision; see Section 9.9.2
date_trunc('hour', timestamp '2001-02-16 20:38:40')
2001-02-16 20:00:00
date_trunc(text
, timestamp with time zone
, text
)
timestamp with time zone
Truncate to specified precision in the specified time zone; see Section 9.9.2
date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')
2001-02-16 13:00:00+00
date_trunc(text
, interval
)
interval
Truncate to specified precision; see Section 9.9.2
date_trunc('hour', interval '2 days 3 hours 40 minutes')
2 days 03:00:00
extract
(field
from timestamp
)
double precision
Get subfield; see Section 9.9.1
extract(hour from timestamp '2001-02-16 20:38:40')
20
extract
(field
from interval
)
double precision
Get subfield; see Section 9.9.1
extract(month from interval '2 years 3 months')
3
isfinite(date
)
boolean
Test for finite date (not +/-infinity)
isfinite(date '2001-02-16')
true
isfinite(timestamp
)
boolean
Test for finite time stamp (not +/-infinity)
isfinite(timestamp '2001-02-16 21:28:30')
true
isfinite(interval
)
boolean
Test for finite interval
isfinite(interval '4 hours')
true
justify_days(interval
)
interval
Adjust interval so 30-day time periods are represented as months
justify_days(interval '35 days')
1 mon 5 days
justify_hours(interval
)
interval
Adjust interval so 24-hour time periods are represented as days
justify_hours(interval '27 hours')
1 day 03:00:00
justify_interval(interval
)
interval
Adjust interval using justify_days
and justify_hours
, with additional sign adjustments
justify_interval(interval '1 mon -1 hour')
29 days 23:00:00
make_date(
year
int
, month
int
, day
int
)
date
Create date from year, month and day fields
make_date(2013, 7, 15)
2013-07-15
make_interval(
years
int
DEFAULT 0, months
int
DEFAULT 0, weeks
int
DEFAULT 0, days
int
DEFAULT 0, hours
int
DEFAULT 0, mins
int
DEFAULT 0, secs
double precision
DEFAULT 0.0)
interval
Create interval from years, months, weeks, days, hours, minutes and seconds fields
make_interval(days => 10)
10 days
make_time(
hour
int
, min
int
, sec
double precision
)
time
Create time from hour, minute and seconds fields
make_time(8, 15, 23.5)
08:15:23.5
make_timestamp(
year
int
, month
int
, day
int
, hour
int
, min
int
, sec
double precision
)
timestamp
Create timestamp from year, month, day, hour, minute and seconds fields
make_timestamp(2013, 7, 15, 8, 15, 23.5)
2013-07-15 08:15:23.5
make_timestamptz(
year
int
, month
int
, day
int
, hour
int
, min
int
, sec
double precision
, [ timezone
text
])
timestamp with time zone
Create timestamp with time zone from year, month, day, hour, minute and seconds fields; if timezone
is not specified, the current time zone is used
make_timestamptz(2013, 7, 15, 8, 15, 23.5)
2013-07-15 08:15:23.5+01
now()
timestamp with time zone
Current date and time (start of current transaction); see Section 9.9.4
statement_timestamp()
timestamp with time zone
Current date and time (start of current statement); see Section 9.9.4
timeofday()
text
Current date and time (like clock_timestamp
, but as a text
string); see Section 9.9.4
transaction_timestamp()
timestamp with time zone
Current date and time (start of current transaction); see Section 9.9.4
to_timestamp(double precision
)
timestamp with time zone
Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to timestamp
to_timestamp(1284352323)
2010-09-13 04:32:03+00
In addition to these functions, the SQL OVERLAPS
operator is supported:
This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. When a pair of values is provided, either the start or the end can be written first; OVERLAPS
automatically takes the earlier value of the pair as the start. Each time period is considered to represent the half-open interval start
<=
time
<
end
, unless start
and end
are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.