将表示时间戳的字符串转换为 PostgreSQL 中的实际时间戳？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13605630/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert a string representing a timestamp to an actual timestamp in PostgreSQL?
提问by mum
In PostgreSQL: I convert stringto timestampwith to_timestamp():
在PostgreSQL：我转换string到timestamp用to_timestamp()：
select * from ms_secondaryhealthcarearea
where to_timestamp((COALESCE(update_datetime, '19900101010101'),'YYYYMMDDHH24MISS')
> to_timestamp('20121128191843','YYYYMMDDHH24MISS')
But I get this error:
但我收到此错误：
ERROR: syntax error at end of input
LINE 1: ...H24MISS') >to_timestamp('20121128191843','YYYYMMDDHH24MISS')
^
********** Error **********
ERROR: syntax error at end of input
SQL state: 42601
Character: 176
Why? How to convert a stringto timestamp?
为什么？如何将 a 转换string为timestamp？
回答by ppeterka
One too many opening brackets. Try this:
一个太多的左括号。试试这个：
select *
from ms_secondaryhealthcarearea
where to_timestamp(COALESCE(update_datetime, '19900101010101'),'YYYYMMDDHH24MISS') >to_timestamp('20121128191843','YYYYMMDDHH24MISS')
You had two opening brackets at to_timestamp:
你在 to_timestamp 有两个左括号：
where to_timestamp((COA.. -- <-- the second one is not needed!
回答by Erwin Brandstetter
@ppeterkahas pointed out the syntax error.
@ppeterka指出了语法错误。
The more pressing question is: Why store timestampdata as string to begin with? If your circumstances allow, consider converting the column to its proper type:
更紧迫的问题是：为什么将timestamp数据存储为字符串？如果您的情况允许，请考虑将列转换为其正确类型：
ALTER TABLE ms_secondaryhealthcarearea
ALTER COLUMN update_datetime TYPE timestamp
USING to_timestamp(update_datetime,'YYYYMMDDHH24MISS');
Or use timestamptz- depending on your requirements.
或使用timestamptz- 取决于您的要求。
回答by George Siggouroglou
Another way to convert a string to a timestamp type of PostgreSql is the above,
另一种将字符串转换为 PostgreSql 时间戳类型的方法是上面的，
SELECT to_timestamp('23-11-1986 06:30:00', 'DD-MM-YYYY hh24:mi:ss')::timestamp without time zone;
回答by Christiaan Westerbeek
I had the same requirement as how I read the title. How to convert an epoch timestamp as text to a real timestamp. In my case I extracted one from a json object. So I ended up with a timestamp as text with milliseconds
我的要求与我阅读标题的方式相同。如何将纪元时间戳作为文本转换为真实时间戳。就我而言，我从 json 对象中提取了一个。所以我最终得到了一个时间戳作为带毫秒的文本
'1528446110978'(GMT: Friday, June 8, 2018 8:21:50.978 AM)
'1528446110978'（格林威治标准时间：2018 年 6 月 8 日星期五 8:21:50.978 AM）
This is what I tried. Just the latter (ts_ok_with_ms) is exactly right.
这是我尝试过的。只是后者 (ts_ok_with_ms) 是完全正确的。
SELECT
data->>'expiration' AS expiration,
pg_typeof(data->>'expiration'),
-- to_timestamp(data->>'expiration'), < ERROR: function to_timestamp(text) does not exist
to_timestamp(
(data->>'expiration')::int8
) AS ts_wrong,
to_timestamp(
LEFT(
data->>'expiration',
10
)::int8
) AS ts_ok,
to_timestamp(
LEFT(
data->>'expiration',
10
)::int8
) + (
CASE
WHEN LENGTH(data->>'expiration') = 13
THEN RIGHT(data->>'expiration', 3) ELSE '0'
END||' ms')::interval AS ts_ok_with_ms
FROM (
SELECT '{"expiration": 1528446110978}'::json AS data
) dummy
This is the (transposed) record that is returned:
这是返回的（转置）记录：
expiration 1528446110978
pg_typeof text
ts_wrong 50404-07-12 12:09:37.999872+00
ts_ok 2018-06-08 08:21:50+00
ts_ok_with_ms 2018-06-08 08:21:50.978+00
I'm sure I overlooked a simpler version of how to get from a timestamp string in a json object to a real timestamp with ms (ts_ok_with_ms), but I hope this helps nonetheless.
我确定我忽略了如何从 json 对象中的时间戳字符串获取带有 ms (ts_ok_with_ms) 的真实时间戳的更简单版本，但我希望这仍然有所帮助。
Update: Here's a function for your convenience.
更新：为了您的方便，这里有一个功能。
CREATE OR REPLACE FUNCTION data.timestamp_from_text(ts text)
RETURNS timestamptz
LANGUAGE SQL AS
$$
SELECT to_timestamp(LEFT(ts, 10)::int8) +
(
CASE
WHEN LENGTH(ts) = 13
THEN RIGHT(ts, 3) ELSE '0'
END||' ms'
)::interval
$$;
