Re: Assigning a timestamp without timezone to a timestamp

Hi
Does any one have any ideas for the following problem?

Two tables both the have open and close columns that are timestamp or
timestamp with time zone.

One row in first table represents the corporate office default open and
close times for all stores relative to the store?s own time zone for a
particular day.

The second table represents the specific open and close time for a specific
store for a specific day, occasionally a store?s hours can be different from
the corporate default.

Table1:
open_time timestamp
close_time timestamp

Table2:
store_number int
open_time timestamp with timezone
close_time timestamp with timezone

I would like to be able to initialize table 2 from table 1.

Suppose I had a store table that contained

Store_table:
Store_number int
Store_tz char(03)

I would like to do something like:

Insert into Table2
Select S.store_number
,cast(T1.open_time as timestamp with timezone at S.Store_tz)
,cast(T1.close_time as timestamp with timezone at S.Store_tz)
from Store_table S, Table1 T1

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6613652
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On Mon, Oct 02, 2006 at 08:15:56PM -0700, chrisj wrote:
> Two tables both the have open and close columns that are timestamp or
> timestamp with time zone.

I think the best answer is to convert the one table to timestamptz,
and always enter explicitly the time zone with it (since you're going
to know the corporate timezone anyway, right?). This way, you don't
have to worry about the client's timezone setting, and you always get
the right answer. For instance:

test=# SHOW TimeZone ;
TimeZone
----------
EST5EDT
(1 row)

test=# SELECT '2006-10-03 09:00:00-00'::timestamptz;
timestamptz
------------------------
2006-10-03 05:00:00-04
(1 row)

This has the other advantage that if an office moves, its "open time"
in history doesn't need to change, and you don't need external
knowledge about what the office time zone is, because that's encoded
in the timestamp.

In general, I think timestamps without timezones are just a bad
idea.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin

Hi Andrew,

I do appreciate your reply and we agree on two things timestamp without
timezone should be avoided and the timestamps in table 2 should definately
be "with timezone".

I have no problem changing the timestamps in table 1 to "with timezone", but
I do not see how this solves my problem (maybe I am just thick).

the timestamps in table 1 are not the open and close times for the corporate
location, but they are the directive to all store locations saying: "In the
context of the timezone your store is located in, these are the hours you
should be open.

For example the corporate office may be on the east coast and they are
saying that on December 24,2006 you should open at 9am and close at 1pm.
Stores in California should open at 9:00am Pacific time and stores in New
York should open at 9am EDT.

If I did not appreciate the full implication of your answer please be
patient with me sometimes I am slow but I usually get there.

Andrew Sullivan wrote:
>
> On Mon, Oct 02, 2006 at 08:15:56PM -0700, chrisj wrote:
>> Two tables both the have open and close columns that are timestamp or
>> timestamp with time zone.
>
> I think the best answer is to convert the one table to timestamptz,
> and always enter explicitly the time zone with it (since you're going
> to know the corporate timezone anyway, right?). This way, you don't
> have to worry about the client's timezone setting, and you always get
> the right answer. For instance:
>
> test=# SHOW TimeZone ;
> TimeZone
> ----------
> EST5EDT
> (1 row)
>
> test=# SELECT '2006-10-03 09:00:00-00'::timestamptz;
> timestamptz
> ------------------------
> 2006-10-03 05:00:00-04
> (1 row)
>
> This has the other advantage that if an office moves, its "open time"
> in history doesn't need to change, and you don't need external
> knowledge about what the office time zone is, because that's encoded
> in the timestamp.
>
> In general, I think timestamps without timezones are just a bad
> idea.
>
> A
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> I remember when computers were frustrating because they *did* exactly what
> you told them to. That actually seems sort of quaint now.
> --J.D. Baldwin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6621346
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
> location, but they are the directive to all store locations saying: "In the
> context of the timezone your store is located in, these are the hours you
> should be open.

Ah. Well, then, right, it _does_ have to be timezone free. That's
actually the only case I'd use that. Sorry, I'm dim, and didn't
understand properly what you were doing. (I read the "relative to
the store's own time zone" to refer to the corporate office. No, I
don't know why, either. Told you I'm dim.)

Anyway, here's something that worked for me (expanding this into your
case ought not to be too tricky):

testing=# SELECT * from storetz ;
id | timezone
----+----------
1 | -03
(1 row)

testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
09:00'||"timezone" as timestamp from storetz where id = 1) as a;
timestamp
------------------------
2006-10-03 12:00:00+00
(1 row)

A
--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
When my information changes, I alter my conclusions. What do you do sir?
--attr. John Maynard Keynes

Brilliant, elegant and simple !!

I can't wait to try it (don't have access to Postgres 9-5 EDT) !!

thank-you !!

Andrew Sullivan wrote:
>
> On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
>> location, but they are the directive to all store locations saying: "In
>> the
>> context of the timezone your store is located in, these are the hours
>> you
>> should be open.
>
> Ah. Well, then, right, it _does_ have to be timezone free. That's
> actually the only case I'd use that. Sorry, I'm dim, and didn't
> understand properly what you were doing. (I read the "relative to
> the store's own time zone" to refer to the corporate office. No, I
> don't know why, either. Told you I'm dim.)
>
> Anyway, here's something that worked for me (expanding this into your
> case ought not to be too tricky):
>
> testing=# SELECT * from storetz ;
> id | timezone
> ----+----------
> 1 | -03
> (1 row)
>
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
> ------------------------
> 2006-10-03 12:00:00+00
> (1 row)
>
> A
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> When my information changes, I alter my conclusions. What do you do sir?
> --attr. John Maynard Keynes
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6622976
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Hi, Chris,
Hi, Andrew,

Chrisj wrote:

> please be patient with me sometimes I am slow but I usually get there.

Andrew Sullivan wrote:
> Sorry, I'm dim,

> Told you I'm dim.

That's just plain wrong. You guys are using PostgreSQL, and that's the
proof that you're the brightest people on the planet. :-)

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Thanks Markus,

But I can't even take credit for that, my business partner suggested using
Postgres.

I have been a DB2 DBA most of my professional life (25 years) until
recently, and a huge proponent of DB2 against the likes of Oracle and
MS-SQL.

So far I am very impressed with Postgres but there is a lot more in Postgres
to get one's head around. I certainly appreciate having people like you and
Andrew to help me along.

God Bless,
- chris

>From: Markus Schaber <schabi(at)logix-tt(dot)com>
>To: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>
>CC: chrisj <chrisj(dot)wood(at)sympatico(dot)ca>, pgsql-sql(at)postgresql(dot)org
>Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp
>Date: Wed, 04 Oct 2006 11:07:40 +0200
>
>Hi, Chris,
>Hi, Andrew,
>
>Chrisj wrote:
>
> > please be patient with me sometimes I am slow but I usually get there.
>
>
>Andrew Sullivan wrote:
> > Sorry, I'm dim,
>
> > Told you I'm dim.
>
>
>That's just plain wrong. You guys are using PostgreSQL, and that's the
>proof that you're the brightest people on the planet. :-)
>
>
>HTH,
>Markus
>--
>Markus Schaber | Logical Tracking&Tracing International AG
>Dipl. Inf. | Software Development GIS
>
>Fight against software patents in Europe! www.ffii.org
>www.nosoftwarepatents.org

Hi, Christopher,

christopher wood wrote:

> But I can't even take credit for that, my business partner suggested
> using Postgres.

So I guess he's a smart one, too. :-)

At least as long as he understands that free software does not mean a
TCO of zero dollars, that's the main mistake when businesses try to jump
on the free software train.

> I have been a DB2 DBA most of my professional life (25 years) until
> recently, and a huge proponent of DB2 against the likes of Oracle and
> MS-SQL.

DB2 is not the worst one, AFAICT.

And as a long-time DBA, you know that administration of a real DBMS is
not "install and forget", but lots of fine-tuning and ongoing care.

> So far I am very impressed with Postgres but there is a lot more in
> Postgres to get one's head around. I certainly appreciate having people
> like you and Andrew to help me along.

PostgreSQL is neither perfect, nor a one-fits-all solution. (Hey, that's
the reason for loads of individuals and companies to develop new
releases and extensions, after all. :-)

But it's a stable DBMS providing most features one would expect and use,
and it has a very supportive community, and commercial supporters and
niche-derivates.

As long as you want to keep learning, and don't hesitate getting
involved, PostgreSQL will offer you a satisfying experience.

Regards,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Hi Markus,

what is AFAICT ?

In the commercial space, I believe DB2 is one of the best

>From: Markus Schaber <schabi(at)logix-tt(dot)com>
>Reply-To: PostgreSQL SQL List <pgsql-sql(at)postgresql(dot)org>
>To: pgsql-sql(at)postgresql(dot)org
>CC: christopher wood <chrisj(dot)wood(at)sympatico(dot)ca>
>Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp
>Date: Wed, 04 Oct 2006 17:44:49 +0200
>
>Hi, Christopher,
>
>christopher wood wrote:
>
> > But I can't even take credit for that, my business partner suggested
> > using Postgres.
>
>So I guess he's a smart one, too. :-)
>
>At least as long as he understands that free software does not mean a
>TCO of zero dollars, that's the main mistake when businesses try to jump
>on the free software train.
>
> > I have been a DB2 DBA most of my professional life (25 years) until
> > recently, and a huge proponent of DB2 against the likes of Oracle and
> > MS-SQL.
>
>DB2 is not the worst one, AFAICT.
>
>And as a long-time DBA, you know that administration of a real DBMS is
>not "install and forget", but lots of fine-tuning and ongoing care.
>
> > So far I am very impressed with Postgres but there is a lot more in
> > Postgres to get one's head around. I certainly appreciate having people
> > like you and Andrew to help me along.
>
>PostgreSQL is neither perfect, nor a one-fits-all solution. (Hey, that's
>the reason for loads of individuals and companies to develop new
>releases and extensions, after all. :-)
>
>But it's a stable DBMS providing most features one would expect and use,
>and it has a very supportive community, and commercial supporters and
>niche-derivates.
>
>As long as you want to keep learning, and don't hesitate getting
>involved, PostgreSQL will offer you a satisfying experience.
>
>Regards,
>Markus
>--
>Markus Schaber | Logical Tracking&Tracing International AG
>Dipl. Inf. | Software Development GIS
>
>Fight against software patents in Europe! www.ffii.org
>www.nosoftwarepatents.org

Hi, Christopher,

christopher wood wrote:

> what is AFAICT ?

"As Far As I Can Tell".

It's explained in the "Jargon File":
http://www.catb.org/jargon/html/A/AFAIK.html

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Hi
I am also interested in this type of setup. However, in the example
below
I am a little confused as to why the table entry is 1, -3
And the subsequent select statement . I would appreciate an explanation
on the select statement. I do not understand the syntax.
Thanks in advance
Hector Villarreal
SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
09:00'||"timezone" as timestamp from storetz where id = 1) as a;
timestamp

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
Sent: Tuesday, October 03, 2006 7:52 AM
To: chrisj
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp

On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
> location, but they are the directive to all store locations saying:
"In the
> context of the timezone your store is located in, these are the hours
you
> should be open.

Ah. Well, then, right, it _does_ have to be timezone free. That's
actually the only case I'd use that. Sorry, I'm dim, and didn't
understand properly what you were doing. (I read the "relative to
the store's own time zone" to refer to the corporate office. No, I
don't know why, either. Told you I'm dim.)

Anyway, here's something that worked for me (expanding this into your
case ought not to be too tricky):

testing=# SELECT * from storetz ;
id | timezone
----+----------
1 | -03
(1 row)

testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
09:00'||"timezone" as timestamp from storetz where id = 1) as a;
timestamp
------------------------
2006-10-03 12:00:00+00
(1 row)

A
--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
When my information changes, I alter my conclusions. What do you do
sir?
--attr. John Maynard Keynes

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wed, Oct 04, 2006 at 11:04:56AM -0700, Hector Villarreal wrote:
> Hi
> I am also interested in this type of setup. However, in the example
> below
> I am a little confused as to why the table entry is 1, -3

The 1 is an artificial key (it's the criterion in the WHERE clause).
The -03 is the time zone offset. The most reliable way to handle
time zone offsets, I find, is to use the numeric offset from UTC.
That's the way PostgreSQL shows them in some cases, too. On my
system, for instance, I get this for SELECT now() (at the moment):

now
-------------------------------
2006-10-05 14:21:51.507419-04
(1 row)

> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp

So what this does is

SELECT

the column named "timestamp" from relation "a"
cast to timestamp with time zone (the :: is a shorthand for
cast in Postgres)

FROM

a relation called "a"
constituted as (this is that "as a" on the end)

SELECT
the literal string '2006-10-03 09:00'
concatenated to (that's what "||" means)
the column "timezone"
[and call that whole thing "timestamp"
FROM
a relation called "storetz"
WHERE
the storetz row has an id of 1.

So, what you get is a timestamp with a time zone that is built up
from the combination of a timestamp without time zone and some time
zone data that you have.

What's _really_ cool in Postgres about the time handling is that you
can also change your time zone, and find that the data nicely
represents your new time zone too. You can see this in my original
example: I was using GMT, but inserted a timestamp in -03. When I
selected the answer, though, I got one back in GMT (==UTC). So
that's why you see this:

>
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
> ------------------------
> 2006-10-03 12:00:00+00
> (1 row)

2006-10-03 09:00:00-03 == 2006-10-03 12:00:00+00

Hope that helps,
A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun

Hi Hector,

It would probably better to get the explanation from Andrew, but I will do
the best I can.

You asked about the 1 and -3. The 1 would be the store number in my
original scenario and -3 would be the representation of the timezone (three
hours behind Universal Coordinate Time).

I still have not had a chance to implement the solution into my application,
but I am assuming the -3 could also be a mnemonic such as "EDT" I live in
Toronto EDT is Eastern Daylight-savings Time.

As for the syntax of the select, it is simply casting a character
representation of a timestamp concatenated with a character representation
of timezone to timestamptz.

In hindsight it is so simple I can't believe I could not come up with it
myself.

Hector Villarreal wrote:
>
> Hi
> I am also interested in this type of setup. However, in the example
> below
> I am a little confused as to why the table entry is 1, -3
> And the subsequent select statement . I would appreciate an explanation
> on the select statement. I do not understand the syntax.
> Thanks in advance
> Hector Villarreal
> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
>
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: Tuesday, October 03, 2006 7:52 AM
> To: chrisj
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp
>
> On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
>> location, but they are the directive to all store locations saying:
> "In the
>> context of the timezone your store is located in, these are the hours
> you
>> should be open.
>
> Ah. Well, then, right, it _does_ have to be timezone free. That's
> actually the only case I'd use that. Sorry, I'm dim, and didn't
> understand properly what you were doing. (I read the "relative to
> the store's own time zone" to refer to the corporate office. No, I
> don't know why, either. Told you I'm dim.)
>
> Anyway, here's something that worked for me (expanding this into your
> case ought not to be too tricky):
>
> testing=# SELECT * from storetz ;
> id | timezone
> ----+----------
> 1 | -03
> (1 row)
>
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
> ------------------------
> 2006-10-03 12:00:00+00
> (1 row)
>
> A
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> When my information changes, I alter my conclusions. What do you do
> sir?
> --attr. John Maynard Keynes
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6667349
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Hi Andrew,

If only all time zones were fixed offset timezones life would be so much
simpler.

Unfortunately the main area of deployment of my app will beToronto which is
on EDT which is not a fixed offsets timezone. I hope/assume your solution
works with "EDT" instead of "-3", I will test it soon.

Andrew Sullivan wrote:
>
> On Wed, Oct 04, 2006 at 11:04:56AM -0700, Hector Villarreal wrote:
>> Hi
>> I am also interested in this type of setup. However, in the example
>> below
>> I am a little confused as to why the table entry is 1, -3
>
> The 1 is an artificial key (it's the criterion in the WHERE clause).
> The -03 is the time zone offset. The most reliable way to handle
> time zone offsets, I find, is to use the numeric offset from UTC.
> That's the way PostgreSQL shows them in some cases, too. On my
> system, for instance, I get this for SELECT now() (at the moment):
>
> now
> -------------------------------
> 2006-10-05 14:21:51.507419-04
> (1 row)
>
>> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
>> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>> timestamp
>
> So what this does is
>
> SELECT
>
> the column named "timestamp" from relation "a"
> cast to timestamp with time zone (the :: is a shorthand for
> cast in Postgres)
>
> FROM
>
> a relation called "a"
> constituted as (this is that "as a" on the end)
>
> SELECT
> the literal string '2006-10-03 09:00'
> concatenated to (that's what "||" means)
> the column "timezone"
> [and call that whole thing "timestamp"
> FROM
> a relation called "storetz"
> WHERE
> the storetz row has an id of 1.
>
> So, what you get is a timestamp with a time zone that is built up
> from the combination of a timestamp without time zone and some time
> zone data that you have.
>
> What's _really_ cool in Postgres about the time handling is that you
> can also change your time zone, and find that the data nicely
> represents your new time zone too. You can see this in my original
> example: I was using GMT, but inserted a timestamp in -03. When I
> selected the answer, though, I got one back in GMT (==UTC). So
> that's why you see this:
>
>>
>> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
>> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
>> timestamp
>> ------------------------
>> 2006-10-03 12:00:00+00
>> (1 row)
>
> 2006-10-03 09:00:00-03 == 2006-10-03 12:00:00+00
>
> Hope that helps,
> A
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> The fact that technology doesn't work is no bar to success in the
> marketplace.
> --Philip Greenspun
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6667446
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote:
> If only all time zones were fixed offset timezones life would be so much
> simpler.

Indeed.

> Unfortunately the main area of deployment of my app will beToronto which is
> on EDT which is not a fixed offsets timezone. I hope/assume your solution
> works with "EDT" instead of "-3", I will test it soon.

Should do, although you'll need more than EDT. EDT is also fixed:
it's UTC-4. EST5EDT isn't, though, so you could use that (you'd need
to improve your schema, though, because you had char(3) there, and
not all time zones are 3 characters long). But to answer your
question, yes, it works. I just tried it.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier

Thanks for the heads up, I definately need EST5EDT

you saved me twice!!

Andrew Sullivan wrote:
>
> On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote:
>> If only all time zones were fixed offset timezones life would be so much
>> simpler.
>
> Indeed.
>
>> Unfortunately the main area of deployment of my app will beToronto which
>> is
>> on EDT which is not a fixed offsets timezone. I hope/assume your
>> solution
>> works with "EDT" instead of "-3", I will test it soon.
>
> Should do, although you'll need more than EDT. EDT is also fixed:
> it's UTC-4. EST5EDT isn't, though, so you could use that (you'd need
> to improve your schema, though, because you had char(3) there, and
> not all time zones are 3 characters long). But to answer your
> question, yes, it works. I just tried it.
>
> A
>
>
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> Information security isn't a technological problem. It's an economics
> problem.
> --Bruce Schneier
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6668169
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Many thanks Chris,
I am new to Postgresql and was trying to understand the casting
portion. Appreciate it as this makes it useful for many applications
where timezones matter.

Thanks
Hector

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of chrisj
Sent: Thursday, October 05, 2006 1:02 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Assigning a timestamp without timezone to a timestamp

Hi Hector,

It would probably better to get the explanation from Andrew, but I will
do
the best I can.

You asked about the 1 and -3. The 1 would be the store number in my
original scenario and -3 would be the representation of the timezone
(three
hours behind Universal Coordinate Time).

I still have not had a chance to implement the solution into my
application,
but I am assuming the -3 could also be a mnemonic such as "EDT" I live
in
Toronto EDT is Eastern Daylight-savings Time.

As for the syntax of the select, it is simply casting a character
representation of a timestamp concatenated with a character
representation
of timezone to timestamptz.

In hindsight it is so simple I can't believe I could not come up with it
myself.

Hector Villarreal wrote:
>
> Hi
> I am also interested in this type of setup. However, in the example
> below
> I am a little confused as to why the table entry is 1, -3
> And the subsequent select statement . I would appreciate an
explanation
> on the select statement. I do not understand the syntax.
> Thanks in advance
> Hector Villarreal
> SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
>
> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: Tuesday, October 03, 2006 7:52 AM
> To: chrisj
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] Assigning a timestamp without timezone to a
timestamp
>
> On Tue, Oct 03, 2006 at 07:26:52AM -0700, chrisj wrote:
>> location, but they are the directive to all store locations saying:
> "In the
>> context of the timezone your store is located in, these are the
hours
> you
>> should be open.
>
> Ah. Well, then, right, it _does_ have to be timezone free. That's
> actually the only case I'd use that. Sorry, I'm dim, and didn't
> understand properly what you were doing. (I read the "relative to
> the store's own time zone" to refer to the corporate office. No, I
> don't know why, either. Told you I'm dim.)
>
> Anyway, here's something that worked for me (expanding this into your
> case ought not to be too tricky):
>
> testing=# SELECT * from storetz ;
> id | timezone
> ----+----------
> 1 | -03
> (1 row)
>
> testing=# SELECT a.timestamp::timestamptz from (SELECT '2006-10-03
> 09:00'||"timezone" as timestamp from storetz where id = 1) as a;
> timestamp
> ------------------------
> 2006-10-03 12:00:00+00
> (1 row)
>
> A
> --
> Andrew Sullivan | ajs(at)crankycanuck(dot)ca
> When my information changes, I alter my conclusions. What do you do
> sir?
> --attr. John Maynard Keynes
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

--
View this message in context:
http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timest
amp-with-timezone-tf2373845.html#a6667349
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Hi Andrew,

Finally got around to trying to implement your solution.

It works fine with fixed offset timezones, but when I try it with EST5EDT
I get the following:
protocal2=> select start_datetime,cast(cast(cast(start_datetime as
timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as
timestamp(0) with time zone) from reservation A, location B where appt_key
= 7 and locn_key = 102 ;
ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13
09:20:00 EST5EDT"

when I change timezone_ch to EST it works like a charm:
protocal2=> select start_datetime,cast(cast(cast(start_datetime as
timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as
timestamp(0) with time zone) from reservation A, location B where appt_key
= 7 and locn_key = 102 ;
start_datetime | timestamptz
------------------------+------------------------
2006-07-13 09:20:00-04 | 2006-07-13 10:20:00-04
(1 row)

Any thoughts?

On Thu, Oct 05, 2006 at 01:06:00PM -0700, chrisj wrote:
> If only all time zones were fixed offset timezones life would be so much
> simpler.

Indeed.

> Unfortunately the main area of deployment of my app will beToronto which
> is
> on EDT which is not a fixed offsets timezone. I hope/assume your solution
> works with "EDT" instead of "-3", I will test it soon.

Should do, although you'll need more than EDT. EDT is also fixed:
it's UTC-4. EST5EDT isn't, though, so you could use that (you'd need
to improve your schema, though, because you had char(3) there, and
not all time zones are 3 characters long). But to answer your
question, yes, it works. I just tried it.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
Information security isn't a technological problem. It's an economics
problem.
--Bruce Schneier

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6815181
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

chrisj <chrisj(dot)wood(at)sympatico(dot)ca> writes:
> It works fine with fixed offset timezones, but when I try it with EST5EDT
> I get the following:
> ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13
> 09:20:00 EST5EDT"

Try it with "America/New_York". The datetime parser seems to think that
a timezone name shouldn't contain digits ... which is bogus, but we'll
have to think carefully about how to improve it ...

regards, tom lane

Did not seem to help:

protocal2=> select start_datetime,cast(cast(cast(start_datetime as
timestamp(0) without time zone) as varchar)||' '||B.timezone_ch as
timestamp(0) with time zone) from reservation A, location B where appt_key
= 7 and locn_key = 102 ;
ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13
09:20:00 America/New_York"

Tom Lane-2 wrote:
>
> chrisj <chrisj(dot)wood(at)sympatico(dot)ca> writes:
>> It works fine with fixed offset timezones, but when I try it with EST5EDT
>> I get the following:
>> ERROR: invalid input syntax for type timestamp with time zone:
>> "2006-07-13
>> 09:20:00 EST5EDT"
>
> Try it with "America/New_York". The datetime parser seems to think that
> a timezone name shouldn't contain digits ... which is bogus, but we'll
> have to think carefully about how to improve it ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6827636
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

chrisj <chrisj(dot)wood(at)sympatico(dot)ca> writes:
> Did not seem to help:
> ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13
> 09:20:00 America/New_York"

Sorry, I was thinking in terms of CVS HEAD ... the ability to use a full
timezone spec in timestamptz input is new for 8.2. You might be able to
use this, which does work in 8.1:

select '2006-07-13 09:20:00'::timestamp at time zone 'EST5EDT';

regards, tom lane

Thanks Tom that's great!!

When I first saw your solution I thought it was logically going to do
(notice the parentheses):
select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';
which does not help

So I was not hopeful, but when I tried it it did exactly what I needed which
is:
select '2006-07-13 09:20:00'::(timestamp at time zone 'EST5EDT');

My adjusted SQL is:

select start_datetime
, cast(start_datetime as timestamp(0) without time zone)::timestamp at
time zone B.timezone_ch
from reservation A
, location B
where A.appt_key = 7
and B.locn_key = 102;

thank-you so much

Tom Lane-2 wrote:
>
> chrisj <chrisj(dot)wood(at)sympatico(dot)ca> writes:
>> Did not seem to help:
>> ERROR: invalid input syntax for type timestamp with time zone:
>> "2006-07-13
>> 09:20:00 America/New_York"
>
> Sorry, I was thinking in terms of CVS HEAD ... the ability to use a full
> timezone spec in timestamptz input is new for 8.2. You might be able to
> use this, which does work in 8.1:
>
> select '2006-07-13 09:20:00'::timestamp at time zone 'EST5EDT';
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6847852
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

chrisj <chrisj(dot)wood(at)sympatico(dot)ca> writes:
> When I first saw your solution I thought it was logically going to do
> (notice the parentheses):
> select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';
> which does not help

Well, actually, that's exactly what it does. AT TIME ZONE is an
operator that converts timestamp without time zone to timestamp with
time zone (or vice versa). I guess you could easily get confused
here, but AT is not WITH.

> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
> time zone B.timezone_ch

That's redundant --- you're casting the result of the cast to timestamp
(implicitly without time zone), then applying the AT TIME ZONE operator.

regards, tom lane

Hi Tom,

Thanks again, I did not appreciate the dual function of "AT TIME ZONE" when
the input is timestamptz then the function converts from one timezone to
another (not what I wanted),

but when the input is timestamp the function acts more like a cast than a
convert (exactly what I wanted)

I must disagree with your assertion about the redundancy of:
> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
> time zone B.timezone_ch

what I am doing is taking a timestamptz, discarding its timezone, and then
casting it to another timezone

for example from 2006-10-03 09:00:00 NZST to 2006-10-03 09:00:00 EST5EDT

If I am missing a much easier way to accomplish this please let me know.

Tom Lane-2 wrote:
>
> chrisj <chrisj(dot)wood(at)sympatico(dot)ca> writes:
>> When I first saw your solution I thought it was logically going to do
>> (notice the parentheses):
>> select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';
>> which does not help
>
> Well, actually, that's exactly what it does. AT TIME ZONE is an
> operator that converts timestamp without time zone to timestamp with
> time zone (or vice versa). I guess you could easily get confused
> here, but AT is not WITH.
>
>> , cast(start_datetime as timestamp(0) without time zone)::timestamp at
>> time zone B.timezone_ch
>
> That's redundant --- you're casting the result of the cast to timestamp
> (implicitly without time zone), then applying the AT TIME ZONE operator.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

--
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6863766
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.