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.

5 Comments:

At December 10, 2013 at 2:21 PM , Blogger lveronese said...

thumbs up!

 
At December 10, 2013 at 2:42 PM , Anonymous Anonymous said...

Nice. Why not also make_datetime?

 
At December 10, 2013 at 8:00 PM , Anonymous Anonymous said...

What you do not like this

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

 
At December 10, 2013 at 10:53 PM , Blogger Pavel Stěhule said...

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.

 
At March 6, 2014 at 2:04 AM , Blogger Pavel Stěhule said...

make_timestamp will be in 9.4 too

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home