格式中的S
个字符的数量与时间戳格式可以接受的占位符的可能数量有关.这些都是有效的值:
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.832244', 'YYYY-MM-DD"T"HH24:MI:SS.USTZ');
to_timestamp
-------------------------------
2023-08-09 23:39:18.832244+00
(1 row)
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.832244', 'YYYY-MM-DD"T"HH24:MI:SS.USSSTZ');
to_timestamp
-------------------------------
2023-08-09 23:39:18.832244+00
(1 row)
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.832244', 'YYYY-MM-DD"T"HH24:MI:SS.USSSSTZ');
to_timestamp
-------------------------------
2023-08-09 23:39:18.832244+00
(1 row)
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.832244', 'YYYY-MM-DD"T"HH24:MI:SS.USSSSSTZ');
to_timestamp
-------------------------------
2023-08-09 23:39:18.832244+00
如果你在小数点后没有足够的数字,它会抱怨:
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.83', 'YYYY-MM-DD"T"HH24:MI:SS.USSSTZ');
ERROR: source string too short for "US" formatting field
DETAIL: Field requires 6 characters, but only 2 remain.
HINT: If your source string is not fixed-width, try using the "FM" modifier.
postgres=#
TZ
表示输出应追加配置的时区UTC偏移量
要使用FM
前缀,只需将其放在US
部分之前:
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.83', 'YYYY-MM-DD"T"HH24:MI:SS.FMUSSSTZ');
to_timestamp
---------------------------
2023-08-09 23:39:18.83+00
(1 row)
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.83', 'YYYY-MM-DD"T"HH24:MI:SS.FMUSSSSSTZ');
to_timestamp
---------------------------
2023-08-09 23:39:18.83+00
(1 row)
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.83', 'YYYY-MM-DD"T"HH24:MI:SS.FMUSSSSSSTZ');
to_timestamp
---------------------------
2023-08-09 23:39:18.83+00
(1 row)
postgres=# select TO_TIMESTAMP('2023-08-09T23:39:18.834567', 'YYYY-MM-DD"T"HH24:MI:SS.FMUSSTZ');
to_timestamp
-------------------------------
2023-08-09 23:39:18.834567+00
(1 row)