PgSQL将每年的日期恢复为最新状态 [英] PgSQL turning day-of-year back into date

问题描述

我正在尝试确定如何将一年的日期转换为PgSQL中的日期。当我这样做

I am trying to determine how to turn a day-of-year back into a date in PgSQL. When I do this

select date '2013-01-01' + interval '53 days'

我得到一个时间戳：

"2013-02-23 00:00:00"

那我怎么做以下

select extract(date from (date '2013-01-01' + interval '53 days'))

select extract(date from (select date '2013-01-01' + interval '53 days'))

我得到错误：时间戳单位日期无法识别？除了为什么，我该怎么做，我只想获得原始操作结果的日期部分？

I get "ERROR: timestamp units "date" not recognized"? Besides the why, how can I do what I want, which is to only get the date portion of the result of the original operation?

推荐答案

使用

select (date '2013-01-01' + interval '53 days')::date

或

select cast(date '2013-01-01' + interval '53 days' as date)

PostgreSQL的标准SQL函数 extract（） 将用于时间戳，但是a）日期不是extract（）的有效参数，并且b）它返回子字段，而不是子字段的集合。从概念上讲，日期由三个子字段组成：年，月和日。

PostgreSQL's standard SQL function "extract()" will operate on timestamps, but a) "date" isn't a valid argument to extract(), and b) it returns subfields, not a collection of subfields. Conceptually, a date consists of a collection of three subfields: year, month, and day.

select extract(year from current_timestamp),
       extract(month from current_timestamp),
       extract(day from current_timestamp),
       -- Concatenate and cast to type "date".
       (extract(year from current_timestamp) || '-' || 
       extract(month from current_timestamp) || '-' ||
       extract(day from current_timestamp))::date

这篇关于PgSQL将每年的日期恢复为最新状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！