Postgresql: Timestamp from string

Question

 elseif (p_period = 'LAST2CALENDARMONTHS') then
        lMonthInt := 2;

        lLastDay :=  date_trunc('month',current_date - interval '1 month') + INTERVAL '1 month - 1 day' + (interval '1 day - 1 second');
        lFirstDay :=  date_trunc('month',current_date - (lMonthInt*INTERVAL '1 month'));

    elseif (p_period = 'LAST3CALENDARMONTHS') then
        lMonthInt := 3;

        lLastDay :=  date_trunc('month',current_date - interval '1 month') + INTERVAL '1 month - 1 day'+ (interval '1 day - 1 second');
        lFirstDay :=  date_trunc('month',current_date - (lMonthInt*INTERVAL '1 month'));

Using Postgresql, the above code converts a string into a FromDate and Todate, for example when I call LAST2CALENDARMONTHS

It will display fromdate: "2016-08-01 00:00:00" Todate: "2016-09-30 23:59:59"

I want to do the same for calling any month of the year, for example calling JANUARY the results should be:

fromdate: "2016-01-01 00:00:00" Todate: "2016-01-31 23:59:59"

Is something like this possible?

Laurenz Albe · Accepted Answer · 2016-10-24 07:11:46Z

2

To get first and last days of January in current year use:

elseif (p_period = 'JANUARY') then
    lFirstDay :=  make_timestamp(date_part('year', current_date)::int, 1, 1, 0, 0, 0);
    lLastDay :=  lFirstDay + interval '1 month' - interval '1 second';

For PostgreSQL 9.3 and earlier, where make_timestampdoesn't exist, you can use

SELECT date_trunc('year', current_timestamp) + '0 months'

to get the first day of January.

edited Oct 24, 2016 at 7:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

answered Oct 24, 2016 at 6:30

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charl van der byl Over a year ago

I get this now:

Charl van der byl Over a year ago

ERROR: function make_timestamp(integer, integer, integer, integer, integer, integer) does not exist LINE 1: SELECT make_timestamp(date_part('year', current_date)::int, ...

klin Over a year ago

@Charlvanderbyl - You should specify Postgres version number (if historic). Thanks Laurenz.

Collectives™ on Stack Overflow

Postgresql: Timestamp from string

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related