日期/时间类型

openGauss支持的日期/时间类型请参见表1。该类型的操作符和内置函数请参见时间和日期处理函数和操作符

日期/时间类型 - 图1 说明:

如果其他的数据库时间格式和openGauss的时间格式不一致,可通过修改配置参数DateStyle的值来保持一致。

表 1 日期/时间类型

名称

描述

存储空间

DATE

日期和时间。

4字节(兼容模式A下存储空间大小为8字节)

TIME [(p)] [WITHOUT TIME ZONE]

只用于一日内时间。

p表示小数点后的精度,取值范围为0~6。

8字节

TIME [(p)] [WITH TIME ZONE]

只用于一日内时间,带时区。

p表示小数点后的精度,取值范围为0~6。

12字节

TIMESTAMP[(p)] [WITHOUT TIME ZONE]

日期和时间。

p表示小数点后的精度,取值范围为0~6。

8字节

TIMESTAMP[(p)][WITH TIME ZONE]

日期和时间,带时区。TIMESTAMP的别名为TIMESTAMPTZ。

p表示小数点后的精度,取值范围为0~6。

8字节

SMALLDATETIME

日期和时间,不带时区。

精确到分钟,秒位大于等于30秒进一位。

8字节

INTERVAL DAY (l) TO SECOND (p)

时间间隔,X天X小时X分X秒。

  • l:天数的精度,取值范围为0~6。兼容性考虑,目前未实现具体功能。
  • p:秒数的精度,取值范围为0~6。小数末尾的零不显示。

16字节

INTERVAL [FIELDS] [ (p) ]

时间间隔。

  • fields:可以是YEAR,MONTH,DAY,HOUR,MINUTE,SECOND,DAY TO HOUR,DAY TO MINUTE,DAY TO SECOND,HOUR TO MINUTE,HOUR TO SECOND,MINUTE TO SECOND。
  • p:秒数的精度,取值范围为0~6,且fields为SECOND,DAY TO SECOND,HOUR TO SECOND或MINUTE TO SECOND时,参数p才有效。小数末尾的零不显示。

12字节

reltime

相对时间间隔。格式为:

X years X mons X days XX:XX:XX。

采用儒略历计时,规定一年为365.25天,一个月为30天,计算输入值对应的相对时间间隔,输出采用POSTGRES格式。

4字节

abstime

日期和时间。格式为:

YYYY-MM-DD hh:mm:ss+timezone

取值范围为1901-12-13 20:45:53 GMT~2038-01-18 23:59:59 GMT,精度为秒。

4字节

示例:

  1. --创建表。
  2. openGauss=# CREATE TABLE date_type_tab(coll date);
  3. --插入数据。
  4. openGauss=# INSERT INTO date_type_tab VALUES (date '12-10-2010');
  5. --查看数据。
  6. openGauss=# SELECT * FROM date_type_tab;
  7. coll
  8. ---------------------
  9. 2010-12-10 00:00:00
  10. (1 row)
  11. --删除表。
  12. openGauss=# DROP TABLE date_type_tab;
  13. --创建表。
  14. openGauss=# CREATE TABLE time_type_tab (da time without time zone ,dai time with time zone,dfgh timestamp without time zone,dfga timestamp with time zone, vbg smalldatetime);
  15. --插入数据。
  16. openGauss=# INSERT INTO time_type_tab VALUES ('21:21:21','21:21:21 pst','2010-12-12','2013-12-11 pst','2003-04-12 04:05:06');
  17. --查看数据。
  18. openGauss=# SELECT * FROM time_type_tab;
  19. da | dai | dfgh | dfga | vbg
  20. ----------+-------------+---------------------+------------------------+---------------------
  21. 21:21:21 | 21:21:21-08 | 2010-12-12 00:00:00 | 2013-12-11 16:00:00+08 | 2003-04-12 04:05:00
  22. (1 row)
  23. --删除表。
  24. openGauss=# DROP TABLE time_type_tab;
  25. --创建表。
  26. openGauss=# CREATE TABLE day_type_tab (a int,b INTERVAL DAY(3) TO SECOND (4));
  27. --插入数据。
  28. openGauss=# INSERT INTO day_type_tab VALUES (1, INTERVAL '3' DAY);
  29. --查看数据。
  30. openGauss=# SELECT * FROM day_type_tab;
  31. a | b
  32. ---+--------
  33. 1 | 3 days
  34. (1 row)
  35. --删除表。
  36. openGauss=# DROP TABLE day_type_tab;
  37. --创建表。
  38. openGauss=# CREATE TABLE year_type_tab(a int, b interval year (6));
  39. --插入数据。
  40. openGauss=# INSERT INTO year_type_tab VALUES(1,interval '2' year);
  41. --查看数据。
  42. openGauss=# SELECT * FROM year_type_tab;
  43. a | b
  44. ---+---------
  45. 1 | 2 years
  46. (1 row)
  47. --删除表。
  48. openGauss=# DROP TABLE year_type_tab;

日期输入

日期和时间的输入几乎可以是任何合理的格式,包括ISO-8601格式、SQL-兼容格式、传统POSTGRES格式或者其它的形式。系统支持按照日、月、年的顺序自定义日期输入。如果把DateStyle参数设置为MDY就按照“月-日-年”解析,设置为DMY就按照“日-月-年”解析,设置为YMD就按照“年-月-日”解析。

日期的文本输入需要加单引号包围,语法如下:

type [ ( p ) ] ‘value’

可选的精度声明中的p是一个整数,表示在秒域中小数部分的位数。表2显示了date类型的输入方式。

表 2 日期输入方式

例子

描述

1999-01-08

ISO 8601格式(建议格式),任何方式下都是1999年1月8号。

January 8, 1999

在任何datestyle输入模式下都无歧义。

1/8/1999

有歧义,在MDY模式下是一月八号,在DMY模式下是八月一号。

1/18/1999

MDY模式下是一月十八日,其它模式下被拒绝。

01/02/03

  • MDY模式下的2003年1月2日。
  • DMY模式下的2003年2月1日。
  • YMD模式下的2001年2月3日。

1999-Jan-08

任何模式下都是1月8日。

Jan-08-1999

任何模式下都是1月8日。

08-Jan-1999

任何模式下都是1月8日。

99-Jan-08

YMD模式下是1月8日,否则错误。

08-Jan-99

一月八日,除了在YMD模式下是错误的之外。

Jan-08-99

一月八日,除了在YMD模式下是错误的之外。

19990108

ISO 8601;任何模式下都是1999年1月8日。

990108

ISO 8601;任何模式下都是1999年1月8日。

1999.008

年和年里的第几天。

J2451187

儒略日。

January 8, 99 BC

公元前99年。

示例:

  1. --创建表。
  2. openGauss=# CREATE TABLE date_type_tab(coll date);
  3. --插入数据。
  4. openGauss=# INSERT INTO date_type_tab VALUES (date '12-10-2010');
  5. --查看数据。
  6. openGauss=# SELECT * FROM date_type_tab;
  7. coll
  8. ---------------------
  9. 2010-12-10 00:00:00
  10. (1 row)
  11. --查看日期格式。
  12. openGauss=# SHOW datestyle;
  13. DateStyle
  14. -----------
  15. ISO, MDY
  16. (1 row)
  17. --设置日期格式。
  18. openGauss=# SET datestyle='YMD';
  19. SET
  20. --插入数据。
  21. openGauss=# INSERT INTO date_type_tab VALUES(date '2010-12-11');
  22. --查看数据。
  23. openGauss=# SELECT * FROM date_type_tab;
  24. coll
  25. ---------------------
  26. 2010-12-10 00:00:00
  27. 2010-12-11 00:00:00
  28. (2 rows)
  29. --删除表。
  30. openGauss=# DROP TABLE date_type_tab;

时间

时间类型包括time [ (p) ] without time zone和time [ (p) ] with time zone。如果只写time等效于time without time zone。

如果在time without time zone类型的输入中声明了时区,则会忽略这个时区。

时间输入类型的详细信息请参见表3,时区输入类型的详细信息请参加表4

表 3 时间输入

例子

描述

05:06.8

ISO 8601

4:05:06

ISO 8601

4:05

ISO 8601

40506

ISO 8601

4:05 AM

与04:05一样,AM不影响数值

4:05 PM

与16:05一样,输入小时数必须<= 12

04:05:06.789-8

ISO 8601

04:05:06-08:00

ISO 8601

04:05-08:00

ISO 8601

040506-08

ISO 8601

04:05:06 PST

缩写的时区

2003-04-12 04:05:06 America/New_York

用名称声明的时区

表 4 时区输入

例子

描述

PST

太平洋标准时间(Pacific Standard Time)

America/New_York

完整时区名称

-8:00

ISO 8601与PST的偏移

-800

ISO 8601与PST的偏移

-8

ISO 8601与PST的偏移

示例:

  1. openGauss=# SELECT time '04:05:06';
  2. time
  3. ----------
  4. 04:05:06
  5. (1 row)
  6. openGauss=# SELECT time '04:05:06 PST';
  7. time
  8. ----------
  9. 04:05:06
  10. (1 row)
  11. openGauss=# SELECT time with time zone '04:05:06 PST';
  12. timetz
  13. -------------
  14. 04:05:06-08
  15. (1 row)

特殊值

openGauss支持几个特殊值,在读取的时候将被转换成普通的日期/时间值,请参考表5

表 5 特殊值

输入字符串

适用类型

描述

epoch

date,timestamp

1970-01-01 00:00:00+00 (Unix系统零时)

infinity

timestamp

比任何其他时间戳都晚

-infinity

timestamp

比任何其他时间戳都早

now

date,time,timestamp

当前事务的开始时间

today

date,timestamp

今日午夜

tomorrow

date,timestamp

明日午夜

yesterday

date,timestamp

昨日午夜

allballs

time

00:00:00.00 UTC

  1. --创建表。
  2. openGauss=# CREATE TABLE realtime_type_special(col1 varchar(20), col2 date, col3 timestamp, col4 time);
  3. --插入数据。
  4. openGauss=# INSERT INTO realtime_type_special VALUES('epoch', 'epoch', 'epoch', NULL);
  5. openGauss=# INSERT INTO realtime_type_special VALUES('infinity', NULL, 'epoch', NULL);
  6. openGauss=# INSERT INTO realtime_type_special VALUES('now', 'now', 'now', 'now');
  7. openGauss=# INSERT INTO realtime_type_special VALUES('today', 'today', 'today', NULL);
  8. openGauss=# INSERT INTO realtime_type_special VALUES('tomorrow', 'tomorrow', 'tomorrow', NULL);
  9. openGauss=# INSERT INTO realtime_type_special VALUES('yesterday', 'yesterday', 'yesterday', NULL);
  10. --查看数据
  11. openGauss=# SELECT * FROM realtime_type_special;
  12. col1 | col2 | col3 | col4
  13. -----------+---------------------+----------------------------+-----------------
  14. epoch | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
  15. now | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
  16. today | 2023-02-27 00:00:00 | 2023-02-27 00:00:00 |
  17. tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
  18. yesterday | 2023-02-26 00:00:00 | 2023-02-26 00:00:00 |
  19. (5 rows)
  20. openGauss=# SELECT * FROM realtime_type_special WHERE col3 < 'infinity';
  21. col1 | col2 | col3 | col4
  22. -----------+---------------------+----------------------------+-----------------
  23. epoch | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
  24. now | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
  25. today | 2023-02-27 00:00:00 | 2023-02-27 00:00:00 |
  26. tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
  27. yesterday | 2023-02-26 00:00:00 | 2023-02-26 00:00:00 |
  28. (5 rows)
  29. openGauss=# SELECT * FROM realtime_type_special WHERE col3 > '-infinity';
  30. col1 | col2 | col3 | col4
  31. -----------+---------------------+----------------------------+-----------------
  32. epoch | 1970-01-01 00:00:00 | 1970-01-01 00:00:00 |
  33. now | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
  34. today | 2023-02-27 00:00:00 | 2023-02-27 00:00:00 |
  35. tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
  36. yesterday | 2023-02-26 00:00:00 | 2023-02-26 00:00:00 |
  37. (5 rows)
  38. openGauss=# SELECT * FROM realtime_type_special WHERE col3 > 'now';
  39. col1 | col2 | col3 | col4
  40. ----------+---------------------+---------------------+------
  41. tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
  42. (1 row)
  43. openGauss=# SELECT * FROM realtime_type_special WHERE col3 = 'today';
  44. col1 | col2 | col3 | col4
  45. -------+---------------------+---------------------+------
  46. today | 2023-02-27 00:00:00 | 2023-02-27 00:00:00 |
  47. (1 row)
  48. openGauss=# SELECT * FROM realtime_type_special WHERE col3 = 'tomorrow';
  49. col1 | col2 | col3 | col4
  50. ----------+---------------------+---------------------+------
  51. tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
  52. (1 row)
  53. openGauss=# SELECT * FROM realtime_type_special WHERE col3 > 'yesterday';
  54. col1 | col2 | col3 | col4
  55. ----------+---------------------+----------------------------+-----------------
  56. now | 2023-02-27 11:38:13 | 2023-02-27 11:38:13.032815 | 11:38:13.032815
  57. today | 2023-02-27 00:00:00 | 2023-02-27 00:00:00 |
  58. tomorrow | 2023-02-28 00:00:00 | 2023-02-28 00:00:00 |
  59. (3 rows)
  60. openGauss=# SELECT TIME 'allballs';
  61. time
  62. ----------
  63. 00:00:00
  64. (1 row)
  65. --删除表。
  66. openGauss=# DROP TABLE realtime_type_special;

时间段输入

reltime的输入方式可以采用任何合法的时间段文本格式,包括数字形式(含负数和小数)及时间形式,其中时间形式的输入支持SQL标准格式、ISO-8601格式、POSTGRES格式等。另外,文本输入需要加单引号。

时间段输入的详细信息请参考表6 时间段输入

表 6 时间段输入

输入示例

输出结果

描述

60

2 mons

采用数字表示时间段,默认单位是day,可以是小数或负数。特别的,负数时间段,在语义上,可以理解为“早于多久”。

31.25

1 mons 1 days 06:00:00

-365

-12 mons -5 days

1 years 1 mons 8 days 12:00:00

1 years 1 mons 8 days 12:00:00

采用POSTGRES格式表示时间段,可以正负混用,不区分大小写,输出结果为将输入时间段计算并转换得到的简化POSTGRES格式时间段。

-13 months -10 hours

-1 years -25 days -04:00:00

-2 YEARS +5 MONTHS 10 DAYS

-1 years -6 mons -25 days -06:00:00

P-1.1Y10M

-3 mons -5 days -06:00:00

采用ISO-8601格式表示时间段,可以正负混用,不区分大小写,输出结果为将输入时间段计算并转换得到的简化POSTGRES格式时间段。

-12H

-12:00:00

示例:

  1. --创建表。
  2. openGauss=# CREATE TABLE reltime_type_tab(col1 character(30), col2 reltime);
  3. --插入数据。
  4. openGauss=# INSERT INTO reltime_type_tab VALUES ('90', '90');
  5. openGauss=# INSERT INTO reltime_type_tab VALUES ('-366', '-366');
  6. openGauss=# INSERT INTO reltime_type_tab VALUES ('1975.25', '1975.25');
  7. openGauss=# INSERT INTO reltime_type_tab VALUES ('-2 YEARS +5 MONTHS 10 DAYS', '-2 YEARS +5 MONTHS 10 DAYS');
  8. openGauss=# INSERT INTO reltime_type_tab VALUES ('30 DAYS 12:00:00', '30 DAYS 12:00:00');
  9. openGauss=# INSERT INTO reltime_type_tab VALUES ('P-1.1Y10M', 'P-1.1Y10M');
  10. --查看数据。
  11. openGauss=# SELECT * FROM reltime_type_tab;
  12. col1 | col2
  13. --------------------------------+-------------------------------------
  14. 1975.25 | 5 years 4 mons 29 days
  15. -2 YEARS +5 MONTHS 10 DAYS | -1 years -6 mons -25 days -06:00:00
  16. P-1.1Y10M | -3 mons -5 days -06:00:00
  17. -366 | -1 years -18:00:00
  18. 90 | 3 mons
  19. 30 DAYS 12:00:00 | 1 mon 12:00:00
  20. (6 rows)
  21. --删除表。
  22. openGauss=# DROP TABLE reltime_type_tab;