Re: patch: make_timestamp function

2013/12/17 Jim Nasby <jim(at)nasby(dot)net>

> On 12/15/13, 12:59 PM, Pavel Stehule wrote:
>
>> Why wouldn't we have a version that optionally accepts the timezone?
>> That mirrors what you can currently do with a cast from text, and having to
>> set the GUC if you need a different TZ would be a real PITA.
>>
>>
>> It is not bad idea.
>>
>> What will be format for timezone in this case? Is a doble enough?
>>
>
> Sorry for not seeing this earlier, but no, I think double is barking up
> the wrong tree. It should accept the same timezone identifiers that the
> rest of the system does, like blah AT TIME ZONE foo and SET timezone = foo;
>

I checked a code from datetime parser, and there we are not consistent

postgres=# select '1973-07-15 08:15:55.33+02'::timestamptz;
timestamptz
---------------------------
1973-07-15 07:15:55.33+01
(1 row)

postgres=# select '1973-07-15 08:15:55.33+02.2'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "1973-07-15
08:15:55.33+02.2"
LINE 1: select '1973-07-15 08:15:55.33+02.2'::timestamptz;

It allows only integer

but AT TIME ZONE allows double (but decimal parts is ignored quietly)

postgres=# select make_time(10,20,30) at time zone '+10.2';
timezone
-------------
23:20:30-10

so I propose (and I implemented) a variant with int as time zone

and we can (if we would) implement next one with text as time zone

Regards

Pavel

>
> Specifically, it needs to support things like 'GMT' and 'CST6CDT'.
>
> I can see an argument for another version that accepts numeric so if you
> want to do -11.5 you don't have to wrap it in quotes...
>
> --
> Jim C. Nasby, Data Architect jim(at)nasby(dot)net
> 512.569.9461 (cell) http://jim.nasby.net
>