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 );
   make_interval   
-------------------
 4 days 10:00:01.2
(1 row)

or:

select make_timestamptz( year := 2014, month := 1, mday := 13, hour := 21, min := 50, sec := 0, timezone := 'Asia/Tokyo' );
    make_timestamptz    
------------------------
 2014-01-13 13:50:00+01
(1 row)

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

  1. One comment

  2. # postgresfan
    Mar 8, 2014

    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?

Leave a comment