postgresql 从连接日期和时间列中获取时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17725418/
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
Get a timestamp from concatenating day and time columns
提问by Yasitha
I am having day and time fields in database. I want to get the time-stamp by concatenating the day and time. How to do this in PostgreSQL?
我在数据库中有日期和时间字段。我想通过连接日期和时间来获取时间戳。如何在 PostgreSQL 中做到这一点？
I have done this:
我已经这样做了：
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28');
And it is working fine.
它工作正常。
But when I tried replacing day and time fields I am getting the following error:
但是当我尝试替换日期和时间字段时，出现以下错误：
SELECT EXTRACT(EPOCH FROM TIMESTAMP Day || ' ' || Time);
ERROR: syntax error at or near "Day" LINE 1: ...quest_count > 0 AND (EXTRACT(EPOCH FROM TIMESTAMP Day || ' ' || Time)) > (e...
ERROR: syntax error at or near "Day" LINE 1: ...quest_count > 0 AND (EXTRACT(EPOCH FROM TIMESTAMP Day || ' ' || Time)) > (e...
回答by Erwin Brandstetter
dateand timetypes
date和time类型
If your Dayis of type dateand your Timeis of type time, there is a very simple solution:
如果您Day是 typedate并且您Time是 type time，则有一个非常简单的解决方案：
SELECT EXTRACT(EPOCH FROM (day + time));
You can just add dateand timeto get a timestamp [without time zone](which is interpreted according to the time zone setting of your session).
您只需添加date并time获取一个timestamp [without time zone]（根据会话的时区设置进行解释）。
And, strictly speaking, extracting the epoch is unrelated to your question per se.date+ timeresult in a timestamp, that's it.
而且，严格来说，提取纪元与您的问题本身无关。date+time结果为 a timestamp，就是这样。
String types
字符串类型
If you are talking about string literals or text/ varcharcolumns, use:
如果您在谈论字符串文字或text/varchar列，请使用：
SELECT EXTRACT(EPOCH FROM ('2013-07-18' || ' ' || '21:52:12')::timestamp);
or
或者
SELECT EXTRACT(EPOCH FROM cast('2013-07-18' ||' '|| '21:52:12' AS timestamp));
Your form does notwork
您的格式做不工作
SELECT EXTRACT(EPOCH FROM TIMESTAMP ('2013-07-18' || ' ' || '21:52:12'));This would work:
这会起作用：
SELECT EXTRACT(EPOCH FROM "timestamp" ('2013-07-18' || ' ' || '21:52:12'));
I quote the manual about type casts:
我引用了有关类型转换的手册：
It is also possible to specify a type cast using a function-like syntax:
typename ( expression )However, this only works for types whose names are also valid as function names. For example,
double precisioncannot be used this way, but the equivalentfloat8can. Also, the namesinterval,time, andtimestampcan only be used in this fashion if they are double-quoted, because of syntactic conflicts. Therefore, the use of the function-like cast syntax leads to inconsistencies and should probably be avoided.
也可以使用类似函数的语法指定类型转换：
typename ( expression )但是，这仅适用于名称也可用作函数名称的类型。例如，
double precision不能以这种方式使用，但等效的float8可以。此外，由于语法冲突，名称interval,time和timestamp只能以这种方式使用双引号。因此，使用类似函数的强制转换语法会导致不一致，应该避免。
Bold emphasis mine.
The gist of it: rather use one of the first two syntax variants.
大胆强调我的。
它的要点：而是使用前两种语法变体之一。
回答by Clodoaldo Neto
SELECT EXTRACT(EPOCH FROM (Day || ' ' || Time)::timestamp);
回答by HydrUra
This works fine for me :
这对我来说很好用：
SELECT CONCAT(Day,' ',Time)::timestamp;
