Postgresql - Functions and Operators 函数和运算 - Date/Time

日期时间的函数和运算。

Operator	Example	Result
+	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'

 

Function	Return Type	Description	Example	Result
age(timestamp, timestamp)	interval	计算年龄（时间长度）	age(timestamp '2001-04-10', timestamp '1957-06-13')	43 years 9 mons 27 days
age(timestamp)	interval	计算到当前时间的年龄（时间长度）	age(timestamp '1957-06-13')	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	计算给定单位时间的时间间隔	date_part('hour', timestamp '2001-02-16 20:38:40')	20
date_part(text, interval)	double precision	计算给定单位时间的时间间隔	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	转换时间单位为天(给的时间如果小于天，转化为HH:mm:ss)	justify_days(interval '800 days')	2 years 2 mons 20 days
justify_hours(interval)	interval	转换时间单位为小时(给的时间如果小于小时，转化为HH:mm:ss)	justify_hours(interval '27 hours')	1 day 03:00:00
justify_interval(interval)	interval	将时间间隔转为合适的时间	justify_interval(interval '1 mon -1 hour')	29 days 23:00:00
localtime	time	本地时间
localtimestamp	timestamp	本地日期时间
make_date(year int, month int, dayint)	date	转换时间	make_date(2013, 7, 15)	2013-07-15
make_interval(years int DEFAULT 0,months int DEFAULT 0, weeks intDEFAULT 0, days int DEFAULT 0, hoursint DEFAULT 0, mins int DEFAULT 0,secs double precision DEFAULT 0.0)	interval	转换时间间隔（years, months, days, hours）	make_interval(days => 10)	10 days
make_time(hour int, min int, secdouble precision)	time	输入单个数字，输出时间格式	make_time(8, 15, 23.5)	08:15:23.5
make_timestamp(year int, month int,day int, hour int, min int, secdouble 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, secdouble precision, [ timezone text ])	timestamp with time zone	输入单个数字，输出日期时间格式	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	当前时间
transaction_timestamp()	timestamp with time zone	事务开始的时间
to_timestamp(double precision)	timestamp with time zone	Unix时间戳转换为日期时间	to_timestamp(1284352323)	2010-09-13 04:32:03+00