postgresql 在 pgSQL 中将日期转换为时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45026903/
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
Cast date to timestamp in pgSQL
提问by i am batman
Is there a way to cast a date to timestamp. For an example if I had a date like 2012/05/01, how can I convert it to a timestamp like 2012-05-01 00:00:01
有没有办法将日期转换为时间戳。例如，如果我有一个类似的日期2012/05/01，我如何将其转换为时间戳2012-05-01 00:00:01
回答by mrina713
You can convert it to a timestampwith this code:
您可以timestamp使用以下代码将其转换为 a ：
SELECT current_date::timestamp
It will directly assign the time 00:00:00to current_date.
它将直接将时间分配00:00:00给current_date。
回答by Tim Biegeleisen
You can cast the date column to text and then concatenate with the time portion of the timestamp. In the query below I create a timestamp from the current date.
您可以将日期列转换为文本，然后与时间戳的时间部分连接。在下面的查询中，我从当前日期创建了一个时间戳。
select (cast(current_date as text) || ' 00:00:01'):: timestamp
from yourTable;
Or if we already have a date type, we can simply add on the time component:
或者如果我们已经有一个日期类型，我们可以简单地添加时间组件：
select current_date + '00:00:01'::time
Output:
输出：
11.07.2017 00:00:01
Demo
演示
Update:
更新：
If you just want the difference in months between two dates you can use the following:
如果您只想要两个日期之间的月数差异，您可以使用以下内容：
DATE_PART('month', AGE(end_date, start_date))
Of course, there is no time component involved here, but assuming you were planning to assign the dummy 00:00:01to both timestamps, the result would not change.
当然，这里不涉及时间组件，但假设您计划将虚拟对象分配00:00:01给两个时间戳，结果不会改变。
回答by dnoeth
select cast(current_date as timestamp) + interval '1 second'
Close to Standard SQL, only the interval syntax differs interval '1' second.
接近标准 SQL，只是间隔语法不同interval '1' second。
回答by Oto Shavadze
You can add time part to date
您可以将时间部分添加到日期
select current_date + '00:00:01'::time
