SQL 如何编写 postgresql 查询以仅从表中获取时间戳字段的日期部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2354717/
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
How to write a postgresql query for getting only the date part of timestamp field, from a table
提问by Linto davis
How to write a postgresql query for getting only the date part of timestamp field, from a table
如何编写 postgresql 查询以仅从表中获取时间戳字段的日期部分
回答by Konrad Garus
select DATE(my_field) from my_table;
回答by pilcrow
You have two basic options, each with a number of equivalent expressions. Assuming a TIMESTAMP field named "ts", you can extract the date part:
您有两个基本选项，每个选项都有许多等效的表达式。假设一个名为 的 TIMESTAMP 字段"ts"，您可以提取日期部分：
- By type cast
CAST(ts AS DATE)SQL-compliant syntaxts::DATEHistorical pg syntaxDATE(ts)Actually a function. Note that this syntax is deprecated, per the link above.
- By date/time function
EXTRACT(YEAR FROM ts)DATE_PART('YEAR', ts)
回答by Frank Heikens
Another option would be to cast your timestamp to a date:
另一种选择是将时间戳转换为日期：
SELECT
CAST('2010-01-01 12:12:12' AS date)
选择
CAST('2010-01-01 12:12:12' AS 日期)
回答by Kamini
Following way work for me
以下方式对我有用
CAST(to_timestamp(timestamp_value/1000) AS date) as created_date
CAST(to_timestamp(timestamp_value/1000) AS date) as created_date
