Hi,
We had a little chat about this with Magnus. It's pretty surprising that there's no built-in function to do this, we should consider adding one.
I agree; you should be able to create a timestamp or a date from integer values. Others, apparently, have written code. The implementation I did was pretty rudimentary, but I was going for speed.
If you could overload to_date and to_timestamp, that would be great. For example:
to_date( year ) = year-01-01
to_date( year, month ) = year-month-01
to_date( year, month, day ) = year-month-day
to_timestamp( year, month, day, hour ) = year-month-day hour:00:00.0000 GMT
etc.
construct_timestamp(year int4, month int4, date int4, hour int4, minute int4, second int4, milliseconds int4, timezone text)
Also, "date int4" should be "day int4", to avoid confusion with the date type.
Does it makes sense to use named parameter notation for the first value (the year)? This could be potentially confusing:
to_date() - What would this return? now()? Jan 1st, 1970? 2000?
Similarly, to_timestamp() ...? Seems meaningless without at least a full date and an hour.
Dave