Waiting for 9.4 – Constructors for interval, timestamp, timestamptz

On 4th of March, Alvaro Herrera committed patch:

Constructors for interval, timestamp, timestamptz
Author: Pavel Stěhule, editorialized somewhat by Álvaro Herrera
Reviewed-by: Tomáš Vondra, Marko Tiikkaja
With input from Fabrízio de Royes Mello, Jim Nasby

These are basically three new functions:

  • make_interval
  • make_timestamp
  • make_timestamptz

What is cool about it, that they will prevent things like:

... INTERVAL '$days days'

I.e. concatenation of strings, and then parsing as interval.

What's even better is that they take named arguments. Full list of arguments:

  • make_interval(years, months, weeks, days, hours, mins, secs)
  • make_timestamp(year, month, day, hour, min, sec)
  • make_timestamptz(year, month, day, hour, min, sec, timezone)

All these are integers, with the exception of:

  • sec/secs are double precision floats
  • timezone is string

So, now you can:

SELECT make_interval( days := 4, hours := 10, secs := 1.2 );
 4 days 10:00:01.2
(1 ROW)


SELECT make_timestamptz( YEAR := 2014, MONTH := 1, mday := 13, HOUR := 21, MIN := 50, sec := 0, timezone := 'Asia/Tokyo' );
 2014-01-13 13:50:00+01
(1 ROW)

Cool. That should make some queries nicer to read/maintain. Thanks guys.

One thought on “Waiting for 9.4 – Constructors for interval, timestamp, timestamptz”

  1. Nice!

    @depesz: Thanks for this awesome blog! I’m a reader since more than 3 years now and it’s fantastic! Just want to say thank you 😉

    BTW: I’m just interested – how much (unique) visitors do you have each month on this site?

Comments are closed.