如何在postgres中获得一个月的最后一天? [英] How to get the last day of month in postgres?

问题描述

如何在postgres中找到本月的最后一天?我有一个日期列以格式(YYYYMMDD)存储为数字(18)我正在尝试使用

使其日期

to_date("act_dt",'YYYYMMDD') AS "act date"

然后找到这个日期的最后一天:像这样:

(select (date_trunc('MONTH',to_date("act_dt",'YYYYMMDD')) + INTERVAL '1 MONTH - 1 day')::date)

但它给了我这个错误:

错误:不支持带有月或年部分的间隔值细节:-----------------------------------------------错误:不支持带有月份或年份部分的间隔值代码:8001上下文:间隔月份:1"查询:673376位置:cg_constmanager.cpp:145进程:padbmaster [pid=20937]-----------------------------------------------

有什么帮助吗?

Postgres 版本:

PostgreSQL 8.0.2 on i686-pc-linux-gnu，由 GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.874 编译

解决方案

如果您使用的是 Amazon AWS Redshift，那么您可以使用 Redshift 的 LAST_DAY 函数.虽然 Redshift 基于 PostgreSQL，LAST_DAY 函数在 PostgreSQL 中不可用，用于 PostgreSQL 查看@wspurgin 的回答.

https://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html

<块引用>

LAST_DAY( { 日期 | 时间戳 } )

LAST_DAY 返回包含日期的月份的最后一天的日期.无论日期参数的数据类型如何，返回类型始终为 DATE.

例如:

SELECT LAST_DAY( TO_DATE( act_date, 'YYYYMMDD' ) )

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using

to_date("act_dt",'YYYYMMDD') AS "act date"

then find the last day of this date: like this:

(select (date_trunc('MONTH',to_date("act_dt",'YYYYMMDD')) + INTERVAL '1 MONTH - 1 day')::date)

but it gives me this error:

ERROR: Interval values with month or year parts are not supported
  Detail: 
  -----------------------------------------------
  error:  Interval values with month or year parts are not supported
  code:      8001
  context:   interval months: "1"
  query:     673376
  location:  cg_constmanager.cpp:145
  process:   padbmaster [pid=20937]
  -----------------------------------------------

Any help?

Postgres version:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.874

解决方案

If you're using Amazon AWS Redshift then you can use Redshift's LAST_DAY function. While Redshift is based on PostgreSQL, the LAST_DAY function is not available in PostgreSQL, for a solution for PostgreSQL see @wspurgin's answer.

https://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html

LAST_DAY( { date | timestamp } )

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the data type of the date argument.

For example:

SELECT LAST_DAY( TO_DATE( act_date, 'YYYYMMDD' ) )

这篇关于如何在postgres中获得一个月的最后一天?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！