在日期、时间与文本之间转换

PostgreSQL 中的时间序列分析

Jasmin Ludolf

Content Developer, DataCamp

格式字符串

YYYY：四位年份
MM：两位数字月份
DD：两位数字日期
HH24：24 小时制两位小时
HH12：12 小时制两位小时
HH：12 小时制两位小时
MI：分钟
SS：秒

将字符串转为日期

TO_DATE()：将字符串转为日期
TO_DATE('text string', 'format string')
格式字符串错误会导致错误数据或报错

SELECT
    TO_DATE('2023-01-15', 'YYYY-MM-DD') AS date_1,
    TO_DATE('20231501', 'YYYYDDMM') AS date_2,
    TO_DATE('Jan 15, 2023', 'Mon DD, YYYY') AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

将字符串转为日期

CAST()：将一种数据类型转换为另一种
适用于任意数据类型
CAST('string' AS data_type)

SELECT
    CAST('2023-01-15' AS DATE) AS date_1,
    CAST('20230115' AS DATE) AS date_2,
    CAST('Jan 15, 2023' AS DATE) AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

转换运算符

:: 转换运算符
用法与 CAST() 相同
仅适用于 PostgreSQL

SELECT
    '2023-01-15'::DATE AS date_1,
    '20230115'::DATE AS date_2,
    'Jan 15, 2023'::DATE AS date_3;

|date_1    |date_2    |date_3    |
|----------|----------|----------|
|2023-01-15|2023-01-15|2023-01-15|

转换为日期与时间

TO_TIMESTAMP()：将字符串转为日期时间
TO_TIMESTAMP('string', 'format string')
可含日期、时间和时区

SELECT
    TO_TIMESTAMP('Jan 15, 2023 14:02:01', 'Mon DD, YYYY HH24:MI:SS') AS date_time;

|date_time                |
|-------------------------|
|2023-01-15 14:02:01+01:00|

转换 Unix 时间

可用 TO_TIMESTAMP() 转换 Unix 时间

SELECT
     TO_TIMESTAMP(1483444800) AT TIME ZONE 'UTC' as datetime;

|datetime           |
|-------------------|
|2017-01-03 12:00:00|

提取 Unix 时间

EXTRACT()：提取数值
对 Unix 时间，计算从纪元到时间戳的秒数

SELECT
    EXTRACT(epoch FROM TIMESTAMP '2017-01-03 12:00:00') AS unix_time;

|unix_time |
|----------|
|1483444800|

转换字段

格式一致的字段

-> 使用 TO_DATE 或 TO_TIMESTAMP

date
Jan 15, 2023
Jan 16, 2023
Jan 17, 2023

SELECT TO_DATE(date, 'Mon DD, YYYY');

|date      |
|----------|
|2023-01-15|
|2023-01-16|
|2023-01-17|

转换字段

格式不一的字段

-> 使用 CAST() 或 ::

date
2023-01-15
20230116
Jan 17, 2023

SELECT CAST(date AS DATE);

|date      |
|----------|
|2023-01-15|
|2023-01-16|
|2023-01-17|

将日期/时间转为文本

TO_CHAR()：将日期时间转为文本
TO_CHAR(date or time, 'format string')

SELECT
    timestamp_field,
    TO_CHAR(timestamp_field, 'YYYY-mm-DD HH12:MI:SS') AS timestamp_text
FROM timetable;

|timestamp_field    |timestamp_text     |
|-------------------|-------------------|
|2015-07-14 11:49:00|2015-07-14 11:49:00|
|2020-10-18 20:53:50|2020-10-18 08:53:50|
|2020-12-31 12:59:59|2020-12-31 12:59:59|

验证数据类型

SELECT
    pg_typeof(timestamp_field) AS "type of(timestamp_field)",
    pg_typeof(TO_CHAR(timestamp_field, 'YYYY-mm-DD HH12:MI:SS')) 
    AS "type of(timestamp_text)"
FROM timetable;

|type of(timestamp_field)   |type of(timestamp_text)|
|---------------------------|-----------------------|
|timestamp without time zone|text                   |

不同分隔符

SELECT
    TO_CHAR(timestamp_field, 'YYYY/mm/DD') AS slashes,
    TO_CHAR(timestamp_field, 'YYYY.mm.DD') AS dots,
    TO_CHAR(timestamp_field, '"Year": YYYY "Month": mm "Day": DD') AS labels
FROM timetable;

|slashes   |dots      |labels                      |
|----------|----------|----------------------------|
|2015/07/14|2015.07.14|Year: 2015 Month: 07 Day: 14|
|2020/10/18|2020.10.18|Year: 2020 Month: 10 Day: 18|
|2020/12/31|2020.12.31|Year: 2020 Month: 12 Day: 31|
|2021/01/01|2021.01.01|Year: 2021 Month: 01 Day: 01|

非数字文本

SELECT
    TO_CHAR(timestamp_field, 'Dy, Mon DD, YYYY') AS date
FROM timetable;

|date             |
|-----------------|
|Tue, Jul 14, 2015|
|Sun, Oct 18, 2020|
|Thu, Dec 31, 2020|
|Fri, Jan 01, 2021|

自定义格式

SELECT TO_CHAR(time_field, 'HH24:MI') AS "HH:MM"
FROM timetable;

|HH:MM|
|-----|
|11:49|
|20:53|
|12:59|
|00:01|

AM 和 PM

SELECT
    TO_CHAR(time_field, 'HH12:MI:SS AM') AS "AM",
    TO_CHAR(time_field, 'HH12:MI:SS pm') AS pm
FROM timetable;

|AM         |pm         |
|-----------|-----------|
|11:49:00 AM|11:49:00 am|
|08:53:50 PM|08:53:50 pm|
|12:59:59 PM|12:59:59 pm|
|12:01:02 AM|12:01:02 am|

MDY 格式

SELECT
    TO_CHAR(date_field, 'MM/DD/YYYY') AS "MDY Numeric",
    TO_CHAR(date_field, 'Mon DD, YYYY') AS "MDY Expanded",
    TO_CHAR(timetz_field, 'HH24:MI:SS TZ') AS upper_case,
    TO_CHAR(timetz_field, 'HH24:MI:SS tz') AS lower_case,
    TO_CHAR(timetz_field, 'HH24:MI:SS OF') AS utc_offset
FROM timetable;

|MDY Numeric|MDY Expanded|upper_case  |lower_case  |utc_offset  |
|-----------|------------|------------|------------|------------|
|12/31/2020 |Dec 31, 2020|18:49:00 UTC|18:49:00 utc|18:49:00 +00|

开始练习吧！

PostgreSQL 中的时间序列分析