Thread: Assigning a timestamp without timezone to a timestamp with timezone
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 withtimezone 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@crankycanuck.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@crankycanuck.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@crankycanuck.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@crankycanuck.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@logix-tt.com> >To: Andrew Sullivan <ajs@crankycanuck.ca> >CC: chrisj <chrisj.wood@sympatico.ca>, pgsql-sql@postgresql.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@logix-tt.com> >Reply-To: PostgreSQL SQL List <pgsql-sql@postgresql.org> >To: pgsql-sql@postgresql.org >CC: christopher wood <chrisj.wood@sympatico.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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan Sent: Tuesday, October 03, 2006 7:52 AM To: chrisj Cc: pgsql-sql@postgresql.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@crankycanuck.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 forcast 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@crankycanuck.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@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan > Sent: Tuesday, October 03, 2006 7:52 AM > To: chrisj > Cc: pgsql-sql@postgresql.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@crankycanuck.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@crankycanuck.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@crankycanuck.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@crankycanuck.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@postgresql.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@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of chrisj Sent: Thursday, October 05, 2006 1:02 PM To: pgsql-sql@postgresql.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@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andrew Sullivan > Sent: Tuesday, October 03, 2006 7:52 AM > To: chrisj > Cc: pgsql-sql@postgresql.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@crankycanuck.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@postgresql.orgso 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@crankycanuck.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@postgresql.orgso 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.wood@sympatico.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.wood@sympatico.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@postgresql.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.wood@sympatico.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.wood@sympatico.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.wood@sympatico.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.wood@sympatico.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.