如何获得postgres的月份最后一天？

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

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

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

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

5 个解决方案

#1

Simply by using the last_day function:

只需使用last_day函数:

select last_day(to_date(act_date,'YYYYMMDD'))

PS: Now, i believe that you know that it is very important to choose the right tags for your own question!

PS:现在,我相信您知道为您自己的问题选择正确的标签非常重要!

#2

For anybody coming to this question looking for the Postgres way to do this (not using Redshift), here's how you'd do it:

对于任何人来到这个问题寻找Postgres的方法(不使用Redshift),这是你如何做到这一点:

SELECT (date_trunc('month', '2017-01-05'::date) + interval '1 month' - interval '1 day')::date
AS end_of_month;

Replacing the '2017-01-05' with whatever date you want to use. You can make this into a function like this:

用您想要使用的日期替换'2017-01-05'。你可以把它变成这样的函数:

create function end_of_month(date)
returns date as
$$
select (date_trunc('month', $1) + interval '1 month' - interval '1 day')::date;
$$ language 'sql'
immutable strict;

#3

Okay, so you've got a numeric(18) column containing numbers like 20150118. You can convert that to a date like:

好的,所以你有一个数字(18)列包含像20150118这样的数字。你可以将它转换为如下日期:

to_date(your_date_column::text, 'YYYYMMDD')

From a date, you can grab the last day of the month like:

从某个日期开始,您可以抓住当月的最后一天,例如:

(date_trunc('month', your_date_column) + 
    interval '1 month' - interval '1 day')::date;

Combined, you'd get:

结合起来,你会得到:

select  (date_trunc('month', to_date(act_dt::text, 'YYYYMMDD')) + 
           interval '1 month' - interval '1 day')::date
from    YourTable;

Example at SQL Fiddle.

SQL Fiddle的例子。

#4

For future searches, Redshift does not accept INTERVAL '1 month'. Instead use dateadd(month, 1, date) as documented here.

对于将来的搜索,Redshift不接受INTERVAL'1个月'。而是使用dateadd(month,1,date),如此处所述。

To get the end of the month use: DATEADD(DAY, -1, (DATE_TRUNC('month', DATEADD(MONTH, 1, date))))

要使用月末,请使用:DATEADD(DAY,-1,(DATE_TRUNC('month',DATEADD(MONTH,1,date))))

#5

select to_char(date_trunc('month', now() + '01 Months'::interval) - '01 Days'::interval, 'YYYYmmDD'::text)::numeric as end_period_n

select to_char(date_trunc('month',now()+'01 Months':: interval) - '01 Days':: interval,'YYYYmmDD':: text):: numeric as end_period_n

#1