1

I'm porting some T-SQL stored procs to PL/pgSql and, being very new to PostgreSQL, don't know what helpful utility functions might be available in the pg community. Is there a set of robust date-math functions that "nearly everybody uses" out there somewhere? I don't want to quickly cobble together some date-math functions if there's already a great package out there.

The PostgreSQL date math operators with "natural language" string literal arguments are user-friendly if you're typing a query and you happen to know the interval:

      select now() - interval '1 day'

but if the interval 1 is the result of a calculation involving nested date-math function calls, these string literals are actually not very user-friendly at all, and it would easier to work with a date_add function:

      select dateadd(d, {calculation that returns the interval}, now() )

Thanks

Tim
  • 8,669
  • 31
  • 105
  • 183

3 Answers3

0

In PostgreSQL, you simply add and subtract interval values to datetime values:

'2001-06-27 14:43:21'::TIMESTAMP - '00:10:00'::INTERVAL = '2001-06-27 14:33:21'::TIMESTAMP
'2001-06-27 14:43:21'::TIMESTAMP- '2001-06-27 14:33:21'::TIMESTAMP = '00:10:00'::INTERVAL

For more information, see "Functions and Operators" in the PostgreSQL online docs.

To compute the first day of the month of a date: date_trunc('month', date)

First day of the next month: date_trunc('month', date) + '1 month'::INTERVAL

Add three months to the first day of the month of this date: date_trunc('month', date) + 3*('1 month'::INTERVAL)

The interval is a data type, not a string, and you can do computations with its values.

flup
  • 26,937
  • 7
  • 52
  • 74
  • I've had the docs in front of me. Let me give you an example. I want to subtract from an arbitrary date the number of months that have elapsed since 1/1/1970, and then add that number of months to 1/1/1970 to return the first day of the month in which the arbitrary date falls. Or add a month to the first day of this month to get the first day of the next month, then subtract one day to get the last day of this month. Working with string literals and operators is not as streamlined as working with functions for this sort of stuff. – Tim Jan 30 '13 at 13:23
  • Last day of the month: select date_trunc('month', now()) + interval '1 month' - interval '1 day'; – Colin 't Hart Jan 30 '13 at 13:33
  • Notice that `datetime` is not a Postgresql type. That would be `timestamp`. You are probably confounding with MySQL. I do these things all the time ;) – Clodoaldo Neto Jan 30 '13 at 14:47
0

The "natural language" strings you're talking about are interval literals. Intervals can also be obtained by using date arithmetic.

Surely dateadd can be quite simply emulated in Postgresql as follows:

select d + ({calculation the returns the interval}::text || ' day')::interval

Substitute "month" or "hours" etc as appropriate.

Colin 't Hart
  • 7,372
  • 3
  • 28
  • 51
  • do these casts to string and the concatenation of strings to produce the interval literal, and then the cast from that string to interval perform as well as date math functions that might involve fewer casts? – Tim Jan 30 '13 at 16:49
  • In my experience Postgresql is *very* fast, so I wouldn't worry about those casts. A dateadd function would also have to do lots of manipulations internally. – Colin 't Hart Jan 30 '13 at 17:28
0

Let me give you an example. I want to subtract from an arbitrary date the number of months that have elapsed since 1/1/1970, and then add that number of months to 1/1/1970 to return the first day of the month in which the arbitrary date falls

select (date_trunc('month', '2013-01-30'::date))::date

Or add a month to the first day of this month to get the first day of the next month, then subtract one day to get the last day of this month

select date_trunc('month', '2013-01-30'::date + 1 * interval '1 month')::date - 1

Notice in the above example you can add any number of months by multiplying the interval '1 month' by an integer. You can do that with any interval without manipulating the string '1 month'. So to add or subtract any interval you just:

select current_date + 5 * interval '1 month'

No need for messy string manipulations. You can multiply by fractions also:

select current_timestamp + 3.5 * interval '1 minute'

To add or subtract days to a date type you use an integer:

select current_date + 10
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Thanks. Who would have thunk that `6 * interval '1 month'` was legal :-) Have to dash out the door and cannot try this out yet, but will be back later in the day to check the green arrow. – Tim Jan 30 '13 at 14:33
  • @Tim I think you'll get the hang of doing things "the Postgresql way", it has quite a rich library of functions already. In my experience, code works better and is more maintainable when you adopt the native building blocks rather than trying to force another product's approach. – Colin 't Hart Jan 31 '13 at 12:04
  • @Tim You are not the only one. AFAIK that is only mentioned in a single example in the manual. Most people used to postgresql manipulate the `interval` input string to get the desired results. Your question is important to spread the "multiplicability" :)) of the interval. – Clodoaldo Neto Jan 31 '13 at 12:25
  • Thanks for the help. I am really taking a liking to PostgreSQL. – Tim Jan 31 '13 at 22:36