如何在PostgreSQL中将日期和秒字段转换为时间戳字段？ [英] How do I convert date and seconds fields to a timestamp field in PostgreSQL?

问题描述

我有一个日期字段，它只是文本，还有一个显示午夜以来秒数的字段。

I have a date field, which is just text, and a field showing seconds since midnight.

CREATE TABLE t ("s" text, "d" text, "ts" timestamp)
;   
INSERT INTO t ("s", "d")
VALUES
    (24855, 20130604),
    (24937.7, 20130604)
;

如何将这些字段转换为时间戳，以便可以运行基于时间的查询？

How can I convert these fields into a timestamp, so I can run time-based queries?

查看带有SQL Fiddle的演示：
http://sqlfiddle.com/#!15/67018/2

See a demo with SQL Fiddle: http://sqlfiddle.com/#!15/67018/2

推荐答案

由于您的数据中包含小数部分秒，您需要进行以下调整：

Since your data includes fractional seconds, you need to adjust for those:

UPDATE t
SET    ts = to_timestamp(d||s, 'YYYYMMDDSSSS.MS');

假定毫秒为最大精度。否则雇用 US 微秒。

Assuming milliseconds as the maximum precision. Else employ US for microseconds.

请考虑手册中的精美印刷品：

在从字符串到时间戳的转换中，以毫秒（ MS ）或
微秒（ US ）值用作
小数点后的秒数。例如， to_timestamp（'12：3'，'SS：MS'）不是3
毫秒，而是300，因为转换将其计为12 + 0.3
秒。这意味着对于格式 SS：MS ，输入值 12：3 ，
12:30 和 12：300 指定相同的毫秒数。要获得
三毫秒，必须使用 12：003 ，该转换将
计为12 + 0.003 = 12.003秒。

In a conversion from string to timestamp, millisecond (MS) or microsecond (US) values are used as the seconds digits after the decimal point. For example to_timestamp('12:3', 'SS:MS') is not 3 milliseconds, but 300, because the conversion counts it as 12 + 0.3 seconds. This means for the format SS:MS, the input values 12:3, 12:30, and 12:300 specify the same number of milliseconds. To get three milliseconds, one must use 12:003, which the conversion counts as 12 + 0.003 = 12.003 seconds.

还要注意， to_timestamp（）返回 带有时区的时间戳 ，该时间戳被强制为 timestamp 在更新的上下文中，结果还可以。在其他情况下，您可能需要显式转换或使用此替代方法，从而产生时间戳记：

Also note that to_timestamp() returns timestamp with time zone, which is coerced to timestamp in the context of the update, which turns out all right. For other contexts, you may want to cast explicitly or use this alternative, yielding timestamp:

SELECT d::date + interval '1s' * s::numeric AS ts FROM t;

到目前为止的普通转换（ d :: date ）之所以有效，是因为您的日期采用标准ISO 8601格式-否则我认为是这样。

The plain cast to date (d::date) works because your dates are in standard ISO 8601 format - or so I assume.

这篇关于如何在PostgreSQL中将日期和秒字段转换为时间戳字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！