Pages

▼

Tuesday, December 10, 2013

make_date, make_time functions

Years I miss a functionality for simple constructing dates and times from numeric fields.

There was a more possibilities - one via to_date function

postgres=# select to_date(2014::text || 10::text || 28::text ,'YYYYMMDD');
  to_date   
------------
 2014-10-28
(1 row)

But I was not too happy with it. It works, but it is missing any elegance (and there are a useless type transformations):

I wrote a two functions, that will be available in PostgreSQL 9.4

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f901bb50e33ad95593bb68f7b3b55eb2e47607dc

postgres=# select make_date(2014,10,28);
 make_date  
------------
 2014-10-28
(1 row)

postgres=# select make_time(10,20,30.323);
  make_time   
--------------
 10:20:30.323
(1 row)

Now I am happy - it was my dream.
at

5 comments:

  1. Luca VeroneseDecember 10, 2013 at 2:21 PM

    thumbs up!

    ReplyDelete
  2. AnonymousDecember 10, 2013 at 2:42 PM

    Nice. Why not also make_datetime?

    ReplyDelete
  3. AnonymousDecember 10, 2013 at 8:00 PM

    What you do not like this

    select to_date('20141028','YYYYMMDD');

    ReplyDelete
  4. Pavel StěhuleDecember 10, 2013 at 10:53 PM

    work with timestamp is little bit more difficult, but probably make_timestamp will be there too.

    I dislike to_date for this purpose, because there are lot of useless steps - casting numbers to string, string concatenation, parsing string with dateformat mask. Described functions are fast bypass of this overhead.

    ReplyDelete
  5. Pavel StěhuleMarch 6, 2014 at 2:04 AM

    make_timestamp will be in 9.4 too

    ReplyDelete