PostgreSQL 时间/日期函数和操作符

日期/时间操作符

下表演示了基本算术操作符的行为(+,*, 等):

操作符实例结果
+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'

日期/时间函数

函数返回类型描述实例结果
age(timestamp, timestamp)interval减去参数后的"符号化"结果,使用年和月,不只是使用天age(timestamp '2001-04-10', timestamp '1957-06-13')43 years 9 mons 27 days
age(timestamp)interval从current_date减去参数后的结果(在午夜)age(timestamp '1957-06-13')43 years 8 mons 3 days
clock_timestamp()timestamp with time zone实时时钟的当前时间戳(在语句执行时变化)  
current_datedate当前的日期;  
current_timetime with time zone当日时间;  
current_timestamptimestamp with time zone当前事务开始时的时间戳;  
date_part(text, timestamp)double precision获取子域(等效于extract);date_part('hour', timestamp '2001-02-16 20:38:40')20
date_part(text, interval)double precision获取子域(等效于extract);date_part('month', interval '2 years 3 months')3
date_trunc(text, timestamp)timestamp截断成指定的精度;date_trunc('hour', timestamp '2001-02-16 20:38:40')2001-02-16 20:00:00
date_trunc(text, interval)interval截取指定的精度,date_trunc('hour', interval '2 days 3 hours 40 minutes')2 days 03:00:00
extract(field from         timestamp)double precision获取子域;extract(hour from timestamp '2001-02-16 20:38:40')20
extract(field from         interval)double precision获取子域;extract(month from interval '2 years 3 months')3
isfinite(date)boolean测试是否为有穷日期(不是 +/-无穷)isfinite(date '2001-02-16')true
isfinite(timestamp)boolean测试是否为有穷时间戳(不是 +/-无穷)isfinite(timestamp '2001-02-16 21:28:30')true
isfinite(interval)boolean测试是否为有穷时间间隔isfinite(interval '4 hours')true
justify_days(interval)interval按照每月 30 天调整时间间隔justify_days(interval '35 days')1 mon 5 days
justify_hours(interval)interval按照每天 24 小时调整时间间隔justify_hours(interval '27 hours')1 day 03:00:00
justify_interval(interval)interval使用justify_days和justify_hours调整时间间隔的同时进行正负号调整justify_interval(interval '1 mon -1 hour')29 days 23:00:00
localtimetime当日时间;  
localtimestamptimestamp当前事务开始时的时间戳;  
            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当前事务开始时的时间戳;