patch: make_timestamp function

On Thu, Dec 12, 2013 at 3:11 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

Hello

this patch try to complete a set of functions make_date and make_timestamp.

Could we have the 'make_timestamptz' function too?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

2013/12/12 Fabrízio de Royes Mello <fabriziomello@gmail.com>

On Thu, Dec 12, 2013 at 3:11 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

Hello

this patch try to complete a set of functions make_date and
make_timestamp.

Could we have the 'make_timestamptz' function too?

I though about it. Then there are two questions

a) Could we have a make_timetz function?

b) What type we use for timezone?

Regards

Pavel Stehule

On Fri, Dec 13, 2013 at 7:09 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

I though about it. Then there are two questions

a) Could we have a make_timetz function?

b) What type we use for timezone?

I just think in a function that returns the timestamp with timezone based
on the current 'timezone' setting.

fabrizio=# show timezone;
TimeZone
-------------
Brazil/East
(1 row)

fabrizio=# select '2013-12-13 11:29:45.786937'::timestamptz;
timestamptz
-------------------------------
2013-12-13 11:29:45.786937-02
(1 row)

fabrizio=# set timezone to 'UTC';
SET
fabrizio=# select '2013-12-13 11:29:45.786937'::timestamptz;
timestamptz
-------------------------------
2013-12-13 11:29:45.786937+00
(1 row)

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Thu, Dec 12, 2013 at 08:50:26PM -0200, Fabrízio de Royes Mello wrote:

On Thu, Dec 12, 2013 at 3:11 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

Hello

this patch try to complete a set of functions make_date and make_timestamp.

Could we have the 'make_timestamptz' function too?

Wouldn't this just be:

SELECT make_timestamp(...) at time zone 'foo';

(assuming make_timestamp actually returns a timestamp and not a
timestamptz).

or do you mean something else?

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

He who writes carelessly confesses thereby at the very outset that he does
not attach much importance to his own thoughts.

-- Arthur Schopenhauer

On Fri, Dec 13, 2013 at 3:53 PM, Martijn van Oosterhout <kleptog@svana.org>
wrote:

On Thu, Dec 12, 2013 at 08:50:26PM -0200, Fabrízio de Royes Mello wrote:

On Thu, Dec 12, 2013 at 3:11 PM, Pavel Stehule <pavel.stehule@gmail.com

wrote:

Hello

this patch try to complete a set of functions make_date and

make_timestamp.

Could we have the 'make_timestamptz' function too?

Wouldn't this just be:

SELECT make_timestamp(...) at time zone 'foo';

(assuming make_timestamp actually returns a timestamp and not a
timestamptz).

or do you mean something else?

Your example will convert the timestamp into time zone defined by 'at time
zone...'.

I think the goal of the "make_date/time/timestamp" function series is build
a date/time/timestamp from scratch, so the use of 'make_timestamptz' is to
build a specific timestamp with timezone and don't convert it.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= <fabriziomello@gmail.com> writes:

I think the goal of the "make_date/time/timestamp" function series is build
a date/time/timestamp from scratch, so the use of 'make_timestamptz' is to
build a specific timestamp with timezone and don't convert it.

Yeah; we don't really want to incur an extra timezone rotation just to get
to a timestamptz. However, it's not clear to me if make_timestamptz()
needs to have an explicit zone parameter or not. It could just assume
that you meant the active timezone.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Dec 13, 2013 at 5:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= <fabriziomello@gmail.com>

writes:

I think the goal of the "make_date/time/timestamp" function series is

build

a date/time/timestamp from scratch, so the use of 'make_timestamptz' is

to

build a specific timestamp with timezone and don't convert it.

Yeah; we don't really want to incur an extra timezone rotation just to get
to a timestamptz. However, it's not clear to me if make_timestamptz()
needs to have an explicit zone parameter or not. It could just assume
that you meant the active timezone.

+1. And if you want a different timezone you can just set the 'timezone'
GUC.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On 12/13/2013 02:35 PM, Tom Lane wrote:

=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= <fabriziomello@gmail.com> writes:

I think the goal of the "make_date/time/timestamp" function series is build
a date/time/timestamp from scratch, so the use of 'make_timestamptz' is to
build a specific timestamp with timezone and don't convert it.

Yeah; we don't really want to incur an extra timezone rotation just to get
to a timestamptz. However, it's not clear to me if make_timestamptz()
needs to have an explicit zone parameter or not. It could just assume
that you meant the active timezone.

Why not overload the function, with one version having the explicit TZ
param?

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/13/13 1:49 PM, Fabr�zio de Royes Mello wrote:

On Fri, Dec 13, 2013 at 5:35 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:

=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= <fabriziomello@gmail.com <mailto:fabriziomello@gmail.com>> writes:

I think the goal of the "make_date/time/timestamp" function series is build
a date/time/timestamp from scratch, so the use of 'make_timestamptz' is to
build a specific timestamp with timezone and don't convert it.

Yeah; we don't really want to incur an extra timezone rotation just to get
to a timestamptz. However, it's not clear to me if make_timestamptz()
needs to have an explicit zone parameter or not. It could just assume
that you meant the active timezone.

+1. And if you want a different timezone you can just set the 'timezone' GUC.

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.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello

2013/12/13 Jim Nasby <jim@nasby.net>

On 12/13/13 1:49 PM, Fabrízio de Royes Mello wrote:

On Fri, Dec 13, 2013 at 5:35 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:
tgl@sss.pgh.pa.us>> wrote:

=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= <fabriziomello@gmail.com<mailto:

fabriziomello@gmail.com>> writes:

I think the goal of the "make_date/time/timestamp" function series

is build

a date/time/timestamp from scratch, so the use of 'make_timestamptz'

is to

build a specific timestamp with timezone and don't convert it.

Yeah; we don't really want to incur an extra timezone rotation just to

get

to a timestamptz. However, it's not clear to me if make_timestamptz()
needs to have an explicit zone parameter or not. It could just assume
that you meant the active timezone.

+1. And if you want a different timezone you can just set the 'timezone'
GUC.

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?

last version of this patch attached (without overloading in this moment)

Hello

updated patch

time zone type functions are overloaded now

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

postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, 2);
make_timestamptz
---------------------------
1973-07-15 07:15:55.33+01
(1 row)

postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
make_timestamptz
---------------------------
1973-07-15 08:15:55.33+01
(1 row)

Regards

Pavel

2013/12/15 Pavel Stehule <pavel.stehule@gmail.com>

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;

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@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/12/17 Jim Nasby <jim@nasby.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

Pavel Stehule escribi�:

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

Yeah, I think a constructor should allow a text timezone.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Yeah, I think a constructor should allow a text timezone.

Yes. I think a numeric timezone parameter is about 99% useless,
and if you do happen to need that behavior you can just cast the
numeric to text no?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/12/17 Tom Lane <tgl@sss.pgh.pa.us>

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Yeah, I think a constructor should allow a text timezone.

Yes. I think a numeric timezone parameter is about 99% useless,
and if you do happen to need that behavior you can just cast the
numeric to text no?

yes, it is possible. Although fully numeric API is much more consistent.

Pavel

2013/12/17 Alvaro Herrera <alvherre@2ndquadrant.com>

Pavel Stehule escribió:

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

Yeah, I think a constructor should allow a text timezone.

is there some simple way, how to parse text time zone?

Regards

Pavel

Hello

2013/12/17 Pavel Stehule <pavel.stehule@gmail.com>

2013/12/17 Tom Lane <tgl@sss.pgh.pa.us>

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Yeah, I think a constructor should allow a text timezone.

Yes. I think a numeric timezone parameter is about 99% useless,
and if you do happen to need that behavior you can just cast the
numeric to text no?

yes, it is possible. Although fully numeric API is much more consistent.

I was wrong - there are timezones with minutes like Iran = '1:30';

so int in hours is bad type - so only text is probably best

Pavel

On Tue, Dec 17, 2013 at 06:07:38PM +0100, Pavel Stehule wrote:

Hello

2013/12/17 Pavel Stehule <pavel.stehule@gmail.com>

2013/12/17 Tom Lane <tgl@sss.pgh.pa.us>

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Yeah, I think a constructor should allow a text timezone.

Yes. �I think a numeric timezone parameter is about 99% useless,
and if you do happen to need that behavior you can just cast the
numeric to text no?

yes, it is possible. Although fully numeric API is much more consistent.

I was wrong - there are timezones with minutes like Iran = '1:30';

so int in hours is bad type - so only text is probably best

I think India is the big non-integer timezone offset country:

http://www.timeanddate.com/worldclock/city.html?n=176
UTC/GMT +5:30 hours

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers