How to get the last day of month in postgres?

Question

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

Warison

to_date converts only strings to dates. If your column contains Unix epoch values, you need to use to_timestamp: to_timestamp(act_dt)::date. – Zwick

@AudriusKažukauskas : ERROR: function to_timestamp(numeric) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts. ? – Warison

to_timestamp expects double precision type, try casting your column to it: to_timestamp(act_dt::float8)::date. BTW, could you confirm that this column stores Unix epoch time values? If this is not the case, you should provide explanation in your question what exactly is it storing. – Zwick

You can do your calculation by replacing your INTERVAL '1 MONTH - 1 day' by INTERVAL '1 MONTH' - interval '1 day' – Arhna

@AudriusKažukauskas : the column has values like this act_dt 20131014 20130614 20150124 20110128 20120825 – Warison

OK then, scrap everything I wrote, @Arhna answer should work for you. Although I'd strongly recommend to store date values using date type (of course, if this is not some legacy system, where it's not up to you to decide column types). – Zwick

SELECT (date_trunc('MONTH', Current_Date) + INTERVAL '1 MONTH - 1 day')::DATE; – Wavell

Answer 1

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' ) )

Arhna

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: 674275 any help? – Warison

@HareRamaHareKrishna: what is your exact Postgres version? Please edit your question and add the output of select version() to your question. – Forcer

@VijaykumarHadalgi It should work even for numeric, i don't see problem executing my answer even on 'Pre-histrical' version :s – Arhna

date columns stored as numeric(18) – Warison

@Arhna : When I use the same code as above I am not able to get it right it throws same error as I put in my question? thanks – Warison

@Arhna maybe it's because this is Amazon Redshift, which is only based on Postgres 8.0. – Zwick

Yes , and it change lot of things! You should put RedShift tag at first – Arhna

And don't really know redShift, but try this: select dateadd(day,-1,dateadd(month,1,date_trunc('month',to_date(to_char(act_dt,'99999999'), 'YYYYMMDD')))) – Arhna

@Arhna : I tried it, but it show like this:act_dt 20111024 2011-11-30 00:00:00 20150127 2019-02-28 00:00:00 20150127 2019-02-28 00:00:00 20150127 2019-02-28 00:00:00 20150127 2019-02-28 00:00:00 for 20150127 it gives 2019-02-28? – Warison

Here they are talking about last_day function, could you please try: select last_day(to_date(to_char(act_dt,'999999‌​99'), 'YYYYMMDD')) ? – Arhna

@Arhna : Thanks a ton This works : select last_day(to_date(act_date,'YYYYMMDD')) – Warison

last_day() is not a Postgres function. Although it would work for the original poster (who does say at the end they're running PostgreSQL 8.0.2 on AWS Redshift) it isn't actually an answer to the question they asked, specifically "How to get the last day of month in postgres?" – Continuo

@Continuo Thank you. This is extremely confusing, especially with a contradictory answer like https://mcmap.net/q/338917/-last_day-function-in-postgres. – Respiratory

Answer 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:

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:

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;

EDIT Postgres 11+

Pulling this out of the comments from @Gabriel, you can now combine interval expressions in one interval (which makes things a little shorter):

select (date_trunc('month', now()) + interval '1 month - 1 day')::date as end_of_month;

-- +--------------+
-- | end_of_month |
-- +--------------+
-- | 2021-11-30   |
-- +--------------+
-- (1 row)

Indelicacy

last_day is simplier and also probabily faster than your solution. Why someone should use all this stuff instead of a simple last_day ? – Ene

@giò because last_day is a redshift function. As I said in my answer this is how you do the same thing with PostgreSQL only. For those who come to this question and aren’t using redshift, this is how they can achieve the same thing as the last_day function. As to whether it’s faster/slower, who can say? Feel free to test them yourself and you can inform us all. – Indelicacy

ohh i didn't know that postgres didn't have last_day function, what a shame – Ene

You can also do the interval math within a single interval, at least in PG11+ select date_trunc('month', now()) + interval '1 month -1 day' – Puli

Answer 3

date_trunc('month',current_date) + interval '1 month' - interval '1 day'

Truncating any date or timestamp to the month level will give you the first of the month containing that date. Adding a month gives you the first of the following month. Then, removing a day will give you the date of the last day of the month of the provided date.

Giotto

another alternative: select date_trunc('month', current_date ) - interval '1 day' – Linsang

Answer 4

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

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.

Corenecoreopsis

Andomar: Now() is my integer date,need to make it date then use it in place of now().. something like this is not working (select (date_trunc('MONTH',to_date("act_dt",'YYYYMMDD')) + INTERVAL '1 MONTH - 1 day')::date) it gives me error as above – Warison

@HareRamaHareKrishna: that was obvious from your question, sorry. Updated the answer. – Corenecoreopsis

ERROR: function to_timestamp(numeric) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts. ? – Warison

For me it works with a numeric argument... which version of Postgres are you using? Does to_timestamp(act_dt::float) work? Where ::type is a Postgres type cast. – Corenecoreopsis

hmm.. I use this to convert it to date to_date("act_dt",'YYYYMMDD') – Warison

If you already have a date object, there is no need to use timestamp() on it. Try the updated answer? – Corenecoreopsis

date columns stored as numeric(18) – Warison

Ok, was able to reproduce your problem. Updated answer works for me! – Corenecoreopsis

Answer 5

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

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

Agripina

Answer 6

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

Seventy

Answer 7

Try this:

select date( date_trunc ('month', (current_date + INTERVAL '1 Month '))) -1 ;

hope this help full

Drypoint