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

`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`. — Audrius Kažukauskas, Jan 28 '15 at 07:03
@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. ? — Hare Rama Hare Krishna, Jan 28 '15 at 07:23
`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. — Audrius Kažukauskas, Jan 28 '15 at 07:29
You can do your calculation by replacing your `INTERVAL '1 MONTH - 1 day'` by `INTERVAL '1 MONTH' - interval '1 day'` — Houari, Jan 28 '15 at 07:31
@AudriusKažukauskas : the column has values like this act_dt `20131014 20130614 20150124 20110128 20120825` — Hare Rama Hare Krishna, Jan 28 '15 at 07:40
OK then, scrap everything I wrote, @Houari 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). — Audrius Kažukauskas, Jan 28 '15 at 07:50
SELECT (date_trunc('MONTH', Current_Date) + INTERVAL '1 MONTH - 1 day')::DATE; — Sajeev, Jun 12 '16 at 11:08

wspurgin · Answer 1 · 2021-11-30T14:31:42.650

135

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)

edited Nov 30 '21 at 14:31

answered Aug 17 '17 at 16:11

wspurgin

2,600
2
17
20

last_day is simplier and also probabily faster than your solution. Why someone should use all this stuff instead of a simple last_day ? – giò Apr 19 '18 at 10:11
23

@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. – wspurgin Apr 19 '18 at 12:18
3

ohh i didn't know that postgres didn't have last_day function, what a shame – giò Apr 19 '18 at 15:55
4

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' – Gabriel Dec 02 '20 at 17:31

score 8 · Answer 2 · answered Apr 25 '21 at 14:50

8

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.

answered Apr 25 '21 at 14:50

Christopher Terry

91
1
1

another alternative: select date_trunc('month', current_date ) - interval '1 day' – Shuai Liu Nov 29 '22 at 03:54

score 7 · Accepted Answer · edited Aug 24 '21 at 02:54

7

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

edited Aug 24 '21 at 02:54

Dai

141,631
28
261
374

answered Jan 28 '15 at 07:39

Houari

5,326
3
31
54

`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? – Hare Rama Hare Krishna Jan 28 '15 at 07:48
2

@HareRamaHareKrishna: what is your exact Postgres version? Please edit your question and add the output of `select version()` to your question. – Jan 28 '15 at 07:49
@VijaykumarHadalgi It should work even for `numeric`, i don't see problem executing my answer even on 'Pre-histrical' version :s – Houari Jan 28 '15 at 08:04
`date columns stored as numeric(18) ` – Hare Rama Hare Krishna Jan 28 '15 at 08:05
@Houari : 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 – Hare Rama Hare Krishna Jan 28 '15 at 08:09
@Houari maybe it's because this is Amazon Redshift, which is only based on Postgres 8.0. – Audrius Kažukauskas Jan 28 '15 at 08:16
Yes , and it change lot of things! You should put RedShift tag at first – Houari Jan 28 '15 at 08:24
1

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'))))` – Houari Jan 28 '15 at 08:27
@Houari : 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? – Hare Rama Hare Krishna Jan 28 '15 at 08:46
1

[Here](http://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html) they are talking about `last_day` function, could you please try: `select last_day(to_date(to_char(act_dt,'999999‌99'), 'YYYYMMDD'))` ? – Houari Jan 28 '15 at 09:01
@Houari : Thanks a ton This works : `select last_day(to_date(act_date,'YYYYMMDD'))` – Hare Rama Hare Krishna Jan 28 '15 at 09:07
17

`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?" – Molomby Aug 15 '19 at 06:30
@Molomby Thank you. This is extremely confusing, especially with a contradictory answer like https://stackoverflow.com/a/14229242/2537548. – Z. Cochrane Apr 08 '20 at 02:31

Andomar · Answer 4 · 2015-01-28T08:33:17.370

6

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.

edited Jan 28 '15 at 08:33

answered Jan 28 '15 at 06:52

Andomar

232,371
49
380
404

1

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 ` – Hare Rama Hare Krishna Jan 28 '15 at 06:56
@HareRamaHareKrishna: that was obvious from your question, sorry. Updated the answer. – Andomar Jan 28 '15 at 07:05
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. ? – Hare Rama Hare Krishna Jan 28 '15 at 07:08
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. – Andomar Jan 28 '15 at 07:38
hmm.. I use this to convert it to date `to_date("act_dt",'YYYYMMDD')` – Hare Rama Hare Krishna Jan 28 '15 at 07:46
If you already have a `date` object, there is no need to use `timestamp()` on it. Try the updated answer? – Andomar Jan 28 '15 at 07:51
`date columns stored as numeric(18)` – Hare Rama Hare Krishna Jan 28 '15 at 08:04
Ok, was able to reproduce your problem. Updated answer works for me! – Andomar Jan 28 '15 at 08:28

score 0 · Answer 5 · answered May 03 '17 at 23:38

0

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

answered May 03 '17 at 23:38

Daniel Evanko

9
1

score 0 · Answer 6 · answered Dec 27 '17 at 07:21

0

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

answered Dec 27 '17 at 07:21

Rakesh H B

1
1

How to get the last day of month in postgres?

6 Answers6

EDIT Postgres 11+

Linked

Related