PostgreSQL 时间/日期函数和操作符
日期/时间操作符
下表演示了基本算术操作符的行为(+,*, 等)：
| 操作符 | 例子 | 结果 |
|---|---|---|
| + | date &qpos;2001-09-28&qpos; + integer &qpos;7&qpos; | date &qpos;2001-10-05&qpos; |
| + | date &qpos;2001-09-28&qpos; + interval &qpos;1 hour&qpos; | timestamp &qpos;2001-09-28 01:00:00&qpos; |
| + | date &qpos;2001-09-28&qpos; + time &qpos;03:00&qpos; | timestamp &qpos;2001-09-28 03:00:00&qpos; |
| + | interval &qpos;1 day&qpos; + interval &qpos;1 hour&qpos; | interval &qpos;1 day 01:00:00&qpos; |
| + | timestamp &qpos;2001-09-28 01:00&qpos; + interval &qpos;23 hours&qpos; | timestamp &qpos;2001-09-29 00:00:00&qpos; |
| + | time &qpos;01:00&qpos; + interval &qpos;3 hours&qpos; | time &qpos;04:00:00&qpos; |
| - | - interval &qpos;23 hours&qpos; | interval &qpos;-23:00:00&qpos; |
| - | date &qpos;2001-10-01&qpos; - date &qpos;2001-09-28&qpos; | integer &qpos;3&qpos; (days) |
| - | date &qpos;2001-10-01&qpos; - integer &qpos;7&qpos; | date &qpos;2001-09-24&qpos; |
| - | date &qpos;2001-09-28&qpos; - interval &qpos;1 hour&qpos; | timestamp &qpos;2001-09-27 23:00:00&qpos; |
| - | time &qpos;05:00&qpos; - time &qpos;03:00&qpos; | interval &qpos;02:00:00&qpos; |
| - | time &qpos;05:00&qpos; - interval &qpos;2 hours&qpos; | time &qpos;03:00:00&qpos; |
| - | timestamp &qpos;2001-09-28 23:00&qpos; - interval &qpos;23 hours&qpos; | timestamp &qpos;2001-09-28 00:00:00&qpos; |
| - | interval &qpos;1 day&qpos; - interval &qpos;1 hour&qpos; | interval &qpos;1 day -01:00:00&qpos; |
| - | timestamp &qpos;2001-09-29 03:00&qpos; - timestamp &qpos;2001-09-27 12:00&qpos; | interval &qpos;1 day 15:00:00&qpos; |
| * | 900 * interval &qpos;1 second&qpos; | interval &qpos;00:15:00&qpos; |
| * | 21 * interval &qpos;1 day&qpos; | interval &qpos;21 days&qpos; |
| * | double precision &qpos;3.5&qpos; * interval &qpos;1 hour&qpos; | interval &qpos;03:30:00&qpos; |
| / | interval &qpos;1 hour&qpos; / double precision &qpos;1.5&qpos; | interval &qpos;00:40:00&qpos; |
日期/时间函数
| 函数 | 返回类型 | 描述 | 例子 | 结果 |
|---|---|---|---|---|
age(timestamp, timestamp)
| interval | 减去参数后的"符号化"结果，使用年和月，不只是使用天 | age(timestamp &qpos;2001-04-10&qpos;, timestamp &qpos;1957-06-13&qpos;) | 43 years 9 mons 27 days |
age(timestamp) | interval | 从current_date减去参数后的结果（在午夜） | age(timestamp &qpos;1957-06-13&qpos;) | 43 years 8 mons 3 days |
clock_timestamp()
| timestamp with time zone | 实时时钟的当前时间戳（在语句执行时变化） | ||
current_date
| date | 当前的日期； | ||
current_time
| time with time zone | 当日时间； | ||
current_timestamp
| timestamp with time zone | 当前事务开始时的时间戳； | ||
date_part(text, timestamp)
| double precision | 获取子域(等效于extract)；
| date_part(&qpos;hour&qpos;, timestamp &qpos;2001-02-16 20:38:40&qpos;) | 20 |
date_part(text, interval) | double precision | 获取子域(等效于extract)；
| date_part(&qpos;month&qpos;, interval &qpos;2 years 3 months&qpos;) | 3 |
date_trunc(text, timestamp)
| timestamp | 截断成指定的精度； | date_trunc(&qpos;hour&qpos;, timestamp &qpos;2001-02-16 20:38:40&qpos;) | 2001-02-16 20:00:00 |
date_trunc(text, interval) | interval | 截取指定的精度， | date_trunc(&qpos;hour&qpos;, interval &qpos;2 days 3 hours 40 minutes&qpos;) | 2 days 03:00:00 |
extract(field from
timestamp)
| double precision | 获取子域； | extract(hour from timestamp &qpos;2001-02-16 20:38:40&qpos;) | 20 |
extract(field from
interval) | double precision | 获取子域； | extract(month from interval &qpos;2 years 3 months&qpos;) | 3 |
isfinite(date)
| boolean | 测试是否为有穷日期(不是 +/-无穷) | isfinite(date &qpos;2001-02-16&qpos;) | true |
isfinite(timestamp) | boolean | 测试是否为有穷时间戳(不是 +/-无穷) | isfinite(timestamp &qpos;2001-02-16 21:28:30&qpos;) | true |
isfinite(interval) | boolean | 测试是否为有穷时间间隔 | isfinite(interval &qpos;4 hours&qpos;) | true |
justify_days(interval)
| interval | 按照每月 30 天调整时间间隔 | justify_days(interval &qpos;35 days&qpos;) | 1 mon 5 days |
justify_hours(interval)
| interval | 按照每天 24 小时调整时间间隔 | justify_hours(interval &qpos;27 hours&qpos;) | 1 day 03:00:00 |
justify_interval(interval)
| interval | 使用justify_days和justify_hours调整时间间隔的同时进行正负号调整 | justify_interval(interval &qpos;1 mon -1 hour&qpos;) | 29 days 23:00:00 |
localtime
| time | 当日时间； | ||
localtimestamp
| timestamp | 当前事务开始时的时间戳； | ||
make_date(year int,
month int,
day int)
| date | 为年、月和日字段创建日期 | 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 | 从年、月、周、天、小时、分钟和秒字段中创建间隔 | make_interval(days := 10) | 10 days |
make_time(hour int,
min int,
sec double precision)
| time | 从小时、分钟和秒字段中创建时间 | 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 | 从年、月、日、小时、分钟和秒字段中创建时间戳 | 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 | 从年、月、日、小时、分钟和秒字段中创建带有时区的时间戳。 没有指定timezone时，使用当前的时区。 | make_timestamptz(2013, 7, 15, 8, 15, 23.5) | 2013-07-15 08:15:23.5+01 |
now()
| timestamp with time zone | 当前事务开始时的时间戳； | ||
statement_timestamp()
| timestamp with time zone | 实时时钟的当前时间戳； | ||
timeofday()
| text | 与clock_timestamp相同，但结果是一个text
字符串；
| ||
transaction_timestamp()
| timestamp with time zone | 当前事务开始时的时间戳； |
