pg库时间索引 pg数据库时间函数

转载

bingfeng 2024-05-24 22:23:03

文章标签 pg库时间索引时间戳 PostgreSQL 时间间隔 文章分类 数据仓库大数据

操作符	例子	结果
+	date '2015-09-28' + integer '7'	date '20-10-05'
+	date '2001-09-28' + interval '1 hour'	timestamp '2001-09-28 01:00'
+	date '2001-09-28' + time '03:00'	timestamp '2001-09-28 03:00'
+	interval '1 day' + interval '1 hour'	interval '1 day 01:00'
+	timestamp '2001-09-28 01:00' + interval '23 hours'	timestamp '2001-09-29 00:00'
+	time '01:00' + interval '3 hours'	time '04:00'
-	- interval '23 hours'	interval '-23:00'
-	date '2001-10-01' - date '2001-09-28'	integer '3'
-	date '2001-10-01' - integer '7'	date '2001-09-24'
-	date '2001-09-28' - interval '1 hour'	timestamp '2001-09-27 23:00'
-	time '05:00' - time '03:00'	interval '02:00'
-	time '05:00' - interval '2 hours'	time '03:00'
-	timestamp '2001-09-28 23:00' - interval '23 hours'	timestamp '2001-09-28 00:00'
-	interval '1 day' - interval '1 hour'	interval '23:00'
-	timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'	interval '1 day 15:00'
*	interval '1 hour' * double precision '3.5'	interval '03:30'
/	interval '1 hour' / double precision '1.5'	interval '00:40'

日期/时间函数

函数	返回类型	描述	例子	结果
age(timestamp,timestamp)	interval	减去参数，生成一个使用年、月的"符号化"的结果	age('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
current_date	date	今天的日期；见 Section 9.9.4
current_time	time with time zone	现在的时间；见 Section 9.9.4
current_timestamp	timestamp with time zone	日期和时间；见 Section 9.9.4
date_part(text,timestamp)	double precision	获取子域(等效于 extract)；又见 Section 9.9.1	date_part('hour', timestamp '2001-02-16 20:38:40')	20
date_part(text,interval)	double precision	获取子域(等效于 extract)；又见 Section 9.9.1	date_part('month', interval '2 years 3 months')	3
date_trunc(text,timestamp)	timestamp	截断成指定的精度；又见Section 9.9.2	date_trunc('hour', timestamp '2001-02-16 20:38:40')	2001-02-16 20:00:00+00
extract(field fromtimestamp)	double precision	获取子域；又见 Section 9.9.1	extract(hour from timestamp '2001-02-16 20:38:40')	20
extract(field frominterval)	double precision	获取子域；又见 Section 9.9.1	extract(month from interval '2 years 3 months')	3
isfinite(timestamp)	boolean	测试有穷时间戳(非无穷)	isfinite(timestamp '2001-02-16 21:28:30')	true
isfinite(interval)	boolean	测试有穷时间间隔	isfinite(interval '4 hours')	true
localtime	time	今日的时间；见 Section 9.9.4
localtimestamp	timestamp	日期和时间；见 Section 9.9.4
now()	timestamp with time zone	当前的日期和时间(等效于 current_timestamp)；见Section 9.9.4
timeofday()	text	当前日期和时间；见Section 9.9.4

EXTRACT

EXTRACT(field from source)

extract 函数从日期/时间数值里抽取子域，比如年或者小时等。source必须是一个类型timestamp，time，或者 interval 的值表达式。（类型为date 的表达式将转换为 timestamp，因此也可以用。）field 是一个标识符或者字串，它指定从源数据中抽取的数域。extract 函数返回类型为double precision

century

世纪。

SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
Result: 20
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 21

第一个世纪从 0001-01-01 00:00:00 AD 开始，尽管那时候人们还不知道这是第一个世纪。这个定义适用于所有使用格里高利历法的国家。没有 0 世纪，我们直接从公元前 1 世纪到公元 1 世纪。如果你认为这个不合理，那么请把抱怨发给：罗马圣彼得教堂，梵蒂冈，教皇收。

PostgreSQL

day

(月分)里的日期域(1-31)

SELECT EXTRACT(DAY from TIMESTAMP '2001-02-16 20:38:40');
Result: 16
decade

年份域除以10

SELECT EXTRACT(DECADE from TIMESTAMP '2001-02-16 20:38:40');
Result: 200
dow

每周的星期号(0 - 6；星期天是 0)(仅用于 timestamp)

SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 5

请注意 extract 的星期几编号和 to_char函数的不同。

doy

一年的第几天(1 -365/366)(仅用于 timestamp)

SELECT EXTRACT(DOY from TIMESTAMP '2001-02-16 20:38:40');
Result: 47
epoch

对于 date 和 timestamp 数值而言，是自 1970-01-01 00:00:00 以来的秒数(结果可能是负数。)；对于interval

SELECT EXTRACT(EPOCH from TIMESTAMP '2001-02-16 20:38:40');
Result: 982352320

SELECT EXTRACT(EPOCH from INTERVAL '5 days 3 hours');
Result: 442800

下面是把 epoch 值转换回时间戳的方法：

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720 * INTERVAL '1 second';
hour

小时域 (0 - 23)

SELECT EXTRACT(HOUR from TIMESTAMP '2001-02-16 20:38:40');
Result: 20
microseconds

秒域，包括小数部分，乘以 1,000,000。请注意它包括全部的秒。

SELECT EXTRACT(MICROSECONDS from TIME '17:12:28.5');
Result: 28500000
millennium

千年。

SELECT EXTRACT(MILLENNIUM from TIMESTAMP '2001-02-16 20:38:40');
Result: 3

20世纪（19xx年）里面的年份在第二个千年里。第三个千年从 2001 年一月一日开始。

PostgreSQL

milliseconds

秒域，包括小数部分，乘以 1000。请注意它包括完整的秒。

SELECT EXTRACT(MILLISECONDS from TIME '17:12:28.5');
Result: 28500
minute

分钟域 (0 - 59)

SELECT EXTRACT(MINUTE from TIMESTAMP '2001-02-16 20:38:40');
Result: 38
month

对于 timestamp 数值，它是一年里的月份数(1 - 12)；对于 interval

SELECT EXTRACT(MONTH from TIMESTAMP '2001-02-16 20:38:40');
Result: 2

SELECT EXTRACT(MONTH from INTERVAL '2 years 3 months');
Result: 3

SELECT EXTRACT(MONTH from INTERVAL '2 years 13 months');
Result: 1
quarter

该天所在的该年的季度(1 - 4)(仅用于 timestamp)

SELECT EXTRACT(QUARTER from TIMESTAMP '2001-02-16 20:38:40');
Result: 1
second

秒域，包括小数部分 (0 - 59[1])

SELECT EXTRACT(SECOND from TIMESTAMP '2001-02-16 20:38:40');
Result: 40

SELECT EXTRACT(SECOND from TIME '17:12:28.5');
Result: 28.5
timezone

与 UTC 的时区偏移，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区。

timezone_hour

时区偏移的小时部分。

timezone_minute

时区偏移的分钟部分。

week

该天在所在的年份里是第几周。根据定义 (ISO 8601)，一年的第一周包含该年的一月四日。(ISO-8601的周从星期一开始。)换句话说，一年的第一个星期四在第一周。（只用于timestamp

因此，一月的头几天可能是前一年的第五十二或者第五十三周。比如，2005-01-01 是 2004 年的第五十三周，而2006-01-01

SELECT EXTRACT(WEEK from TIMESTAMP '2001-02-16 20:38:40'); Result: 7

year

年份域。要记住这里没有 0 AD，所以从 AD 年里抽取BC

SELECT EXTRACT(YEAR from TIMESTAMP '2001-02-16 20:38:40'); Result: 2001

extract 函数主要的用途是做运算用。对于用于显示的日期/时间数值格式化，参阅Section 9.8。

date_part 函数是在传统的Ingres 函数的基础上制作的(该函数等效于SQL 标准函数 extract)∶

date_part('field', source)

请注意这里的 field 参数必须是一个字串值，而不是一个名字。有效的 date_part 数域名和用于 extract

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); Result: 16 SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); Result: 4

9.9.2. date_trunc

date_trunc 函数在概念上和用于数字的 trunc

date_trunc('field', source)

source 是类型timestamp 的值表达式(类型date 和 time 的数值都分别自动转换成timestamp或者interval)。用field 选择对该时间戳数值选用什么样的精度进行截断)。返回的数值是 timestamp 类型或者interval，所有小于选定的精度的域都设置为零(或者一，如果是日期和月份域的话)。

field

microseconds
milliseconds
second
minute
hour
day
week
month
year
decade
century
millennium

例子：

SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-02-16 20:00:00+00 SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-01-01 00:00:00+00

9.9.3.AT TIME ZONE

AT TIME ZONE 构造允许把时间戳转换成不同的时区。Table 9-27 显示了其变体。

Table 9-27. AT TIME ZONE变体

表达式	返回类型	描述
timestamp without time zoneAT TIME ZONE zone	timestamp with time zone	把给出的不带时区的时间戳转换成指定时区的时间
timestamp with time zoneAT TIME ZONE zone	timestamp without time zone	把给出的带时区的时间转换为转换成给定时区的时间
time with time zoneAT TIME ZONE zone	time with time zone	在时区之间转换当地时间

在这些表达式里，我们需要的 zone 可以声明为文本串（比如，'PST'）或者一个时间间隔（比如，INTERVAL '-08:00'）。在文本的情况下，可用的时区名字要么在Table B-4要么在Table B-4 里面。

例子（假设本地时区是 PST8PDT）：

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40

第一个例子接受一个无时区的时间戳然后把她解释成 MST 时间（UTC-7）生成 UTC 时间戳，然后这个时间转换为 PST（UTC-8）来显示。第二个例子接受一个声明为 EST（UTC-5）的时间戳，然后把它转换成 MST（UTC-7）的当地时间。

函数timezone(zone,timestamp)等效于 SQL 兼容的构造timestampAT TIME ZONE zone。

9.9.4. 当前日期/时间

我们可以使用下面的函数获取当前的日期和/或时间∶

CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TIME (precision)
CURRENT_TIMESTAMP (precision)
LOCALTIME
LOCALTIMESTAMP
LOCALTIME (precision)
LOCALTIMESTAMP (precision)
 
 
CURRENT_TIME 和 CURRENT_TIMESTAMP 带有时区值；LOCALTIME 和 LOCALTIMESTAMP
CURRENT_TIME ，CURRENT_TIMESTAMP，LOCALTIME 和LOCALTIMESTAMP

注意: 在 PostgreSQL

一些例子：

SELECT CURRENT_TIME;
Result: 14:39:53.662522-05

SELECT CURRENT_DATE;
Result: 2001-12-23

SELECT CURRENT_TIMESTAMP;
Result: 2001-12-23 14:39:53.662522-05

SELECT CURRENT_TIMESTAMP(2);
Result: 2001-12-23 14:39:53.66-05

SELECT LOCALTIMESTAMP;
Result: 2001-12-23 14:39:53.662522

函数 now() 是传统的PostgreSQL 和CURRENT_TIMESTAMP

还有一件事提醒大家，那就是 CURRENT_TIMESTAMP 和相关的函数把时间当做当前事务的开始返回；在事务运行的时候，它们的数值并不改变。我们认为这是一个特性：目的是为了允许一个事务在"当前"时间上有连贯的概念，这样在同一个事务离得多个修改可以有同样的时间戳。

注意: 许多其它数据库系统更频繁地更新这些数值。

还有一个 timeofday() 函数，它返回实时的时间值，并且会在事务里随时间前进。由于历史原因，它返回一个text字串，而不是timestamp

SELECT timeofday();
Result: Sat Feb 17 19:07:32.000126 2001 EST

所有日期/时间类型还接受特殊的文本值 now，用于声明当前的日期和时间。因此，下面三个都返回相同的结果∶

SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now'; -- 用在 DEFAULT 里是不正确的

提示: 在创建表声明一个DEFAULT值的时候你是不会想用第三种形式的。因为系统将在分析这个常量的时候把now 转换成一个 timestamp，因此在需要缺省值的时候，就会使用创建表的时间!而头两种形式要到实际使用缺省值的时候才计算，因为它们是函数调用。因此它们可以给出插入时间行的时候需要的缺省行为。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。