PostgreSQL 教程: to_timestamp 函数

九月 20, 2023

摘要:在本教程中,您将学习如何使用 PostgreSQL 的TO_TIMESTAMP()函数,将字符串转换为基于指定格式的时间戳。

PostgreSQL 的TO_TIMESTAMP()函数根据指定的格式将字符串转换为时间戳

语法

下面说明了TO_TIMESTAMP()函数的语法:

TO_TIMESTAMP(timestamp, format)

参数

TO_TIMESTAMP()函数需要两个参数:

1) timestamp

timestamp是一个字符串,表示采用format指定格式的时间戳值。

2) format

timestamp参数的格式。

要构造format字符串,您可以使用以下模板模式来格式化日期和时间值。

模式 描述
Y,YYY 带逗号的年份,4 位数字
YYYY 4 位数字的年份
YYY 年份的最后 3 位数字
YY 年份的最后 2 位数字
Y 年份的最后 1 位数字
IYYY ISO 8601 周编号年份(4 位或更多位)
IYY ISO 8601 周编号年份的最后 3 位数字
IY ISO 8601 周编号年份的最后 2 位数字
I ISO 8601 周编号年份的最后 1 位数字
BC、bc、AD 或 ad 不带句点的纪元指示器
B.C.、b.c.、A.D. 或 a.d. 带句点的纪元指示器
MONTH 全大写的英文月份名称
Month 全首字母大写的英文月份名称
month 全小写的英文月份名称
MON 大写的月份缩写名称,例如 JAN、FEB 等。
Mon 首字母大写的月份缩写名称,例如 Jan、Feb 等。
mon 小写的月份缩写名称,例如 jan、feb 等。
MM 月份号从 01 到 12
DAY 全大写的日名
Day 首字母大写的日名
day 全小写的日名
DY 大写的缩写日名
Dy 首字母大写的缩写日名
dy 全小写的缩写日名
DDD 一年中的日 (001 - 366)
IDDD ISO 8601 方式的一年中的第几天(001 - 371;该年的第 1 天是第一个 ISO 周的星期一)
DD 一个月中的第几天 (01 - 31)
D 一周中的日,星期日 (1) 至星期六 (7)
ID ISO 8601 规范的一周中的日,星期一 (1) 到星期日 (7)
W 一个月中的第几周 (1-5)(第一周从每月的第一天开始)
WW 一年中的第几周 (1-53)(第一周从一年中的第一天开始)
IW ISO 8601 周编号方式的一年中的第几周(01-53;一年中的第一个星期四位于第 1 周)
CC 世纪,例如 21、22 等。
J 儒略日(自 UTC 时间公元前 4714 年 11 月 24 日午夜 起的整数天)
RM 大写的罗马数字月份 (I–XII; I=一月)
rm 小写的罗马数字月份 (i–xii; i=一月)
HH 一天中的小时(0 - 12)
HH12 一天中的小时(0 - 12)
HH24 一天中的小时(0 - 23)
MI 分钟(0 - 59)
SS 秒(0 - 59)
MS 毫秒(000 - 999)
US 微秒 (000000 - 999999)
SSSS 午夜后的秒数 (0 - 86399)
AM、am、PM 或 pm 正午指示器(无句点)
A.M.、a.m.、P.M. 或 p.m. 正午指示器(带句点)

返回值

PostgreSQL 的TO_TIMESTAMP()函数返回带有时区的时间戳(timestamp with time zone)。

示例

以下语句使用TO_TIMESTAMP()函数将字符串转换为时间戳:

SELECT TO_TIMESTAMP(
    '2017-03-31 9:30:20',
    'YYYY-MM-DD HH:MI:SS'
);

输出:

      to_timestamp
------------------------
 2017-03-31 09:30:20-07
(1 row)

在这个例子中:

  • YYYY 是四位数年份 2017
  • MM 是 03 月份
  • DD 是第 31 天
  • HH 是 9 点
  • MI 是 30 分钟
  • SS 是第 20 秒

备注

\1) 除非使用固定格式全局选项(FX前缀),否则TO_TIMESTAMP()函数会跳过输入字符串中的空格。

此示例在输入字符串中使用多个空格:

SELECT 
    TO_TIMESTAMP('2017     Aug','YYYY MON');

TO_TIMESTAMP()函数只是忽略空格并返回正确的时间戳值:

      to_timestamp
------------------------
 2017-08-01 00:00:00-07
(1 row)

但是,以下示例返回错误:

SELECT 
    TO_TIMESTAMP('2017     Aug','FXYYYY MON');

输出:

ERROR:  invalid value "" for "MON"
DETAIL:  The given value did not match any of the allowed values for this field.
SQL state: 22007

因为FX选项指示TO_TIMESTAMP()接受仅包含一个空格的输入字符串。

\2) TO_TIMESTAMP()函数只进行最少的错误检查来验证输入字符串。它会尝试尽可能将输入字符串转换为有效的时间戳,有时会产生意想不到的结果。

以下示例使用无效的时间戳值:

SELECT 
    TO_TIMESTAMP('2017-02-31 30:8:00', 'YYYY-MM-DD HH24:MI:SS');

它返回一个错误:

ERROR:  date/time field value out of range: "2017-02-31 30:8:00"
SQL state: 22008

\3) 当将字符串转换为时间戳时,TO_TIMESTAMP()函数将毫秒或微秒视为小数点后的秒数。

SELECT 
    TO_TIMESTAMP('01-01-2017 10:2', 'DD-MM-YYYY SS:MS');

结果是:

       to_timestamp
--------------------------
 2017-01-01 00:00:10.2-07

在此示例中,2 不是 2 毫秒,而是 200 毫秒。这意味着:

SELECT 
        TO_TIMESTAMP('01-01-2017 10:2', 'DD-MM-YYYY SS:MS');

SELECT 
        TO_TIMESTAMP('01-01-2017 10:200', 'DD-MM-YYYY SS:MS');

返回相同的结果。

2017-01-01 00:00:10.2-07

要获得 2 毫秒,您必须使用01-01-2017 10:002。在本例中,002被解释为0.002秒,相当于 2 毫秒。

\4) 如果年份少于四位数字,则TO_TIMESTAMP()会将其调整为最接近的年份,例如,99 变为 1999 年,17 变为 2017 年。

SELECT
    TO_TIMESTAMP('12 31 99 12:45', 'MM DD YY HH:MI');

输出是:

      to_timestamp
------------------------
 1999-12-31 00:45:00+07
(1 row)

考虑以下示例:

SELECT
      TO_TIMESTAMP('12 31 16 12:45', 'MM DD YY HH:MI');

最接近 16 的年份是 2016 年,因此,它返回以下结果:

      to_timestamp
------------------------
 2016-12-31 00:45:00-07

在本教程中,您学习了如何使用 PostgreSQL 的TO_TIMESTAMP()函数,将字符串转换为时间戳。