[HACKERS] processing time zone
I had to write obscure code for processing time zone and using it for timestamptz Datum make_timestamptz_at_timezone(PG_FUNCTION_ARGS) { Timestamp timestamp; text *zone; int tz; chartzname[TZ_STRLEN_MAX + 1]; char *lowzone; int type, val; struct pg_tm tt, *tm = tt; fsec_t fsec; TimestampTz result; int session_tz; timestamp = make_timestamp_internal(PG_GETARG_INT32(0), /* year */ PG_GETARG_INT32(1),/* month */ PG_GETARG_INT32(2),/* mday */ PG_GETARG_INT32(3),/* hour */ PG_GETARG_INT32(4),/* min */ PG_GETARG_FLOAT8(5)); /* sec */ if (timestamp2tm(timestamp, NULL, tm, fsec, NULL, NULL) != 0) ereport(ERROR, (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg(timestamp out of range))); zone = PG_GETARG_TEXT_PP(6); text_to_cstring_buffer(zone, tzname, sizeof(tzname)); if (DecodeTimezone(tzname, tz) != 0) { lowzone = downcase_truncate_identifier(tzname, strlen(tzname), false); type = DecodeSpecial(0, lowzone, val); if (type == TZ || type == DTZ) tz = val * MINS_PER_HOUR; else { pg_tz *tzp; tzp = pg_tzset(tzname); if (tzp) tz = DetermineTimeZoneOffset(tm, tzp); else { ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg(time zone \%s\ not recognized, tzname))); tz = 0; /* keep compiler quiet */ } } } elog(NOTICE, entry 0: %d, tz); session_tz = DetermineTimeZoneOffset(tm, session_timezone); PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(timestamp, -tz)); } It works postgres=# select make_timestamptz(2014,12,17,21,06,37.7,'Europe/Moscow') ; make_timestamptz -- 2014-12-17 18:06:37.7+01 (1 row) postgres=# select '2014-12-17 21:06:37.7 Europe/Moscow'::timestamptz; timestamptz -- 2014-12-17 18:06:37.7+01 (1 row) Is some better way, how to parse time zone? Regards Pavel Stehule
[HACKERS] inconsistent time zone formats in log
The xlog code uses two different time zone formats at various times. Here is an example: 2012-12-29 07:04:07.338 EST LOG: database system was interrupted; last known up at 2012-12-29 06:27:02 EST 2012-12-29 07:04:26.347 EST LOG: last completed transaction was at log time 2012-12-29 06:34:24.394802-05 The second format also does not respect log_timezone, which seems a bit of a bug. It's also not clear why we need three different ways to show milliseconds within the space of two lines. Could we make some of this more consistent? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] inconsistent time zone formats in log
On 2012-12-29 07:23:24 -0500, Peter Eisentraut wrote: The xlog code uses two different time zone formats at various times. Here is an example: 2012-12-29 07:04:07.338 EST LOG: database system was interrupted; last known up at 2012-12-29 06:27:02 EST 2012-12-29 07:04:26.347 EST LOG: last completed transaction was at log time 2012-12-29 06:34:24.394802-05 The second format also does not respect log_timezone, which seems a bit of a bug. It's also not clear why we need three different ways to show milliseconds within the space of two lines. One is a pg_time_t (stored in pg_control/ControlFileData), the other is a TimestampTz. Those have completely different code paths for being printed (pg_strftime vs EncodeDateTime) ... I don't want to say its impossible or shouldn't be fixed, just that its not trivial to do so. Greetings, Andres Freund -- Andres Freund 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
Re: [HACKERS] inconsistent time zone formats in log
Andres Freund and...@2ndquadrant.com writes: On 2012-12-29 07:23:24 -0500, Peter Eisentraut wrote: The xlog code uses two different time zone formats at various times. One is a pg_time_t (stored in pg_control/ControlFileData), the other is a TimestampTz. Those have completely different code paths for being printed (pg_strftime vs EncodeDateTime) ... I don't want to say its impossible or shouldn't be fixed, just that its not trivial to do so. Presumably, any fix would involve changing one or the other of those so that we use only one timestamp representation in xlog. I'm not terribly convinced that it's worth worrying about though. Do we need microsecond precision in the database start/stop times? 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
Re: [HACKERS] inconsistent time zone formats in log
On 30/12/12 05:24, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2012-12-29 07:23:24 -0500, Peter Eisentraut wrote: The xlog code uses two different time zone formats at various times. One is a pg_time_t (stored in pg_control/ControlFileData), the other is a TimestampTz. Those have completely different code paths for being printed (pg_strftime vs EncodeDateTime) ... I don't want to say its impossible or shouldn't be fixed, just that its not trivial to do so. Presumably, any fix would involve changing one or the other of those so that we use only one timestamp representation in xlog. I'm not terribly convinced that it's worth worrying about though. Do we need microsecond precision in the database start/stop times? regards, tom lane If I saw an inconsistency like that in a production log, while chasing an apparent case of corruption, I would tend to think that the differences in time might be giving me a hint. Knowing my luck, that will happen to me in a couple of years, after I've forgotten this email tread! Cheers, Gavin
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
Greg Stark st...@mit.edu writes: On Fri, Oct 7, 2011 at 10:10 PM, Merlin Moncure mmonc...@gmail.com wrote: Facts are not subject to copyright but compilations can be. I know it's popular for engineers to play lawyer and I've been guilty of it on many an occasion. But in this case I think you're all *way* oversimplifying the situation and I don't think it's within our ken to be able to come to any clear conclusion. Well, I'm not a lawyer and I'm certainly not volunteering to be counsel for Messrs. Olson et al. But I can recognize a troll when I see one. More to the point, this is an attack on a fundamental piece of open source infrastructure, and I'm quite sure that a lot of large companies will be stepping up to help ensure that it stays open. I feel no need for us to do anything, until and unless there's an adverse court ruling, which I fully expect there will not be. And if there is, we won't be the only ones looking for an alternative solution. 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
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On 10/07/2011 11:02 PM, Greg Stark wrote: All that said I think this is far murkier than you all seem to think. Copyright law is one of the most complex areas of the law and this is one of the least well defined parts of copyright law. Hi Greg: I don't think we all think this issue is clear. Quoting relevant case law and considering what position to hold or what action to take is what I would call due diligence. If somebody wants to hire a lawyer that might be advisable as well. I think wait and see whether this is a true violation is a perfectly valid legal position to hold and is not pretending in any way that this issue is clear... -- Mark Mielkem...@mielke.cc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On 10/05/2011 07:37 AM, Tom Lane wrote: davegda...@sonic.net writes: Postgresql 9.0.4 has the timezone: America/Blanc-Sablon However other sources seem to spell this with an underscore instead of dash: America/Blanc_Sablon I don't know what other sources you're consulting, but Blanc-Sablon is the way it appears in the Olson timezone database, and that's what we follow. Speaking of Olson tz database, I've just stumbled across this post and I thought it would be worthy to report it here: http://blog.joda.org/2011/10/today-time-zone-database-was-closed.html We're not going to get into the business of editorializing on their information. If you want to fool with it locally, look into the .../share/timezone/ directory. 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
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
Andrea Suisani wrote: On 10/05/2011 07:37 AM, Tom Lane wrote: davegda...@sonic.net writes: Postgresql 9.0.4 has the timezone: America/Blanc-Sablon However other sources seem to spell this with an underscore instead of dash: America/Blanc_Sablon I don't know what other sources you're consulting, but Blanc-Sablon is the way it appears in the Olson timezone database, and that's what we follow. Speaking of Olson tz database, I've just stumbled across this post and I thought it would be worthy to report it here: http://blog.joda.org/2011/10/today-time-zone-database-was-closed.html I suppose there is nothing stopping them from attacking people who distribute the database, like Postgres, Red Hat, etc. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
Bruce Momjian br...@momjian.us writes: Andrea Suisani wrote: Speaking of Olson tz database, I've just stumbled across this post and I thought it would be worthy to report it here: http://blog.joda.org/2011/10/today-time-zone-database-was-closed.html I suppose there is nothing stopping them from attacking people who distribute the database, like Postgres, Red Hat, etc. It seems pretty baseless to me: you can't copyright a collection of facts. I think we should do nothing pending a court decision. 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
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Andrea Suisani wrote: Speaking of Olson tz database, I've just stumbled across this post and I thought it would be worthy to report it here: http://blog.joda.org/2011/10/today-time-zone-database-was-closed.html I suppose there is nothing stopping them from attacking people who distribute the database, like Postgres, Red Hat, etc. It seems pretty baseless to me: you can't copyright a collection of facts. I think we should do nothing pending a court decision. Agreed. I am just pointing out the possible exposure. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On 7 October 2011 21:27, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: It seems pretty baseless to me: you can't copyright a collection of facts. I think we should do nothing pending a court decision. Agreed. I am just pointing out the possible exposure. The one interesting case that I can recall were this was tested was this (lifted from Wikipedia): In October 1984, Fred L. Worth, author of The Trivia Encyclopedia, Super Trivia, and Super Trivia II, filed a $300 million lawsuit against the distributors of Trivial Pursuit. He claimed that more than a quarter of the questions in the game's Genus Edition had been taken from his books, even to the point of reproducing typographical errors and deliberately placed misinformation. One of the questions in Trivial Pursuit was What was Columbo's first name? with the answer Philip. That information had been fabricated to catch anyone who might try to violate his copyright.[5] The inventors of Trivial Pursuit acknowledged that Worth's books were among their sources, but argued that this was not improper and that facts are not protected by copyright. The district court judge agreed, ruling in favor of the Trivial Pursuit inventors. The decision was appealed, and in September 1987 the United States Court of Appeals for the Ninth Circuit upheld the ruling.[6] Worth asked the Supreme Court of the United States to review the case, but the Court declined, denying certiorari in March 1988.[7] IANAL, but this seems pretty conclusive to me... -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On 7 October 2011 21:17, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: Andrea Suisani wrote: Speaking of Olson tz database, I've just stumbled across this post and I thought it would be worthy to report it here: http://blog.joda.org/2011/10/today-time-zone-database-was-closed.html I suppose there is nothing stopping them from attacking people who distribute the database, like Postgres, Red Hat, etc. It seems pretty baseless to me: you can't copyright a collection of facts. I think we should do nothing pending a court decision. It's ironic that they're attacking those using these facts when their business is selling fiction poorly disguised as fact. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On Fri, Oct 7, 2011 at 3:33 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 7 October 2011 21:27, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: It seems pretty baseless to me: you can't copyright a collection of facts. I think we should do nothing pending a court decision. Agreed. I am just pointing out the possible exposure. The one interesting case that I can recall were this was tested was this (lifted from Wikipedia): In October 1984, Fred L. Worth, author of The Trivia Encyclopedia, Super Trivia, and Super Trivia II, filed a $300 million lawsuit against the distributors of Trivial Pursuit. He claimed that more than a quarter of the questions in the game's Genus Edition had been taken from his books, even to the point of reproducing typographical errors and deliberately placed misinformation. One of the questions in Trivial Pursuit was What was Columbo's first name? with the answer Philip. That information had been fabricated to catch anyone who might try to violate his copyright.[5] The inventors of Trivial Pursuit acknowledged that Worth's books were among their sources, but argued that this was not improper and that facts are not protected by copyright. The district court judge agreed, ruling in favor of the Trivial Pursuit inventors. The decision was appealed, and in September 1987 the United States Court of Appeals for the Ninth Circuit upheld the ruling.[6] Worth asked the Supreme Court of the United States to review the case, but the Court declined, denying certiorari in March 1988.[7] IANAL, but this seems pretty conclusive to me... Facts are not subject to copyright but compilations can be. However, the arrangement and presentation of the compilation has to be sufficient to have merit protection. For example, the SCOTUS denied copywrite protection to phone books, which I think is entirely relevant to this issue. (BUT INAL). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
My original read of the problem determined (for me personally) that the only way one could be in violation of copyright was if the data was incorrect (i.e. not factual). It presented an interesting contradiction. The only way they could sue is by agreeing that their data is faulty and should not be trusted. :-) The case Merlin refers to below seemed to rule that even faulty information is not a concern. Personally, I think the best choice is to officially state a position on the matter and agree to remove any copyrighted material that has been used without the permission of the copyright owner from PostgreSQL if or when this is ever demonstrated in court. Until that time, the damage to the community by responding to this unproven legal threat would be unreasonable to bear. On 10/07/2011 05:10 PM, Merlin Moncure wrote: The one interesting case that I can recall were this was tested was this (lifted from Wikipedia): In October 1984, Fred L. Worth, author of The Trivia Encyclopedia, Super Trivia, and Super Trivia II, filed a $300 million lawsuit against the distributors of Trivial Pursuit. He claimed that more than a quarter of the questions in the game's Genus Edition had been taken from his books, even to the point of reproducing typographical errors and deliberately placed misinformation. One of the questions in Trivial Pursuit was What was Columbo's first name? with the answer Philip. That information had been fabricated to catch anyone who might try to violate his copyright.[5] The inventors of Trivial Pursuit acknowledged that Worth's books were among their sources, but argued that this was not improper and that facts are not protected by copyright. The district court judge agreed, ruling in favor of the Trivial Pursuit inventors. The decision was appealed, and in September 1987 the United States Court of Appeals for the Ninth Circuit upheld the ruling.[6] Worth asked the Supreme Court of the United States to review the case, but the Court declined, denying certiorari in March 1988.[7] IANAL, but this seems pretty conclusive to me... Facts are not subject to copyright but compilations can be. However, the arrangement and presentation of the compilation has to be sufficient to have merit protection. For example, the SCOTUS denied copywrite protection to phone books, which I think is entirely relevant to this issue. (BUT INAL). -- Mark Mielkem...@mielke.cc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On Fri, Oct 7, 2011 at 4:20 PM, Mark Mielke m...@mark.mielke.cc wrote: My original read of the problem determined (for me personally) that the only way one could be in violation of copyright was if the data was incorrect (i.e. not factual). It presented an interesting contradiction. The only way they could sue is by agreeing that their data is faulty and should not be trusted. :-) The case Merlin refers to below seemed to rule that even faulty information is not a concern. specifically, http://en.wikipedia.org/wiki/Feist_v._Rural merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On Fri, Oct 7, 2011 at 10:10 PM, Merlin Moncure mmonc...@gmail.com wrote: On 7 October 2011 21:27, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: It seems pretty baseless to me: you can't copyright a collection of facts. I think we should do nothing pending a court decision. The one interesting case that I can recall were this was tested was this (lifted from Wikipedia): In October 1984, Fred L. Worth, author of The Trivia Encyclopedia, Super Trivia, and Super Trivia II, filed a $300 million lawsuit against the distributors of Trivial Pursuit. Facts are not subject to copyright but compilations can be. I know it's popular for engineers to play lawyer and I've been guilty of it on many an occasion. But in this case I think you're all *way* oversimplifying the situation and I don't think it's within our ken to be able to come to any clear conclusion. a) Both the trivial pursuit case and the Feist predate a major change to US copyright statutes -- the DMCA. The DMCA implemented the WIPO Copyright Treaty which specifically addressed database compilation copyrights. I do not know how to interpret the language of the DMCA on this and frankly I'm not sure anybody knows since I don't know if there have been any major cases under it yet. If my guess is right the relevant section is 17 U.S.C. §§ 103. I'm not clear that a compilation that was made prior to the DMCA can suddenly acquire copyrights when if it had none before though. b) Both of these cases are US cases. Copyright law varies heavily from country to country despite the Berne and WIPO treaties. c) I don't think that resolving whether the Olson database would be covered even under Feist is so crystal clear as you guys make it out to be. *After* Feist but before the DMCA courts ruled in various cases that phone books and even a baseball score card *did* have enough originality to qualify for copyright. All that said I think this is far murkier than you all seem to think. Copyright law is one of the most complex areas of the law and this is one of the least well defined parts of copyright law. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [OT?] Time-zone database down [was: Re: timezone buglet?]
On Fri, Oct 7, 2011 at 10:02 PM, Greg Stark st...@mit.edu wrote: All that said I think this is far murkier than you all seem to think. Copyright law is one of the most complex areas of the law and this is one of the least well defined parts of copyright law. imposing no natural restrictions have that effect ;) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Time zone definitions to config files
On Tue, Jul 25, 2006 at 10:37:20PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: I'll take a whack at that patch this evening PDT or tomorrow evening at the latest. We're too late in the cycle to go over this, but maybe we can figure out a way to have this data read from the same data source as the pg_timezones VIEW does at compile time. Keeping two such table in synch seems error-prone. Well, the problem is exactly that there is no same data source anymore: the local DBA can customize the timezone list all he wants. We could document what the out-of-the-box settings are, but is it really useful to duplicate that info in the SGML docs? We don't for example provide a copy of psql \df+ output in the SGML docs, and I'm wondering if this isn't kind of the same animal. I'd vote for not including it. The current table is way out of sync already. I first chose the timezones for the Default-set based on this table in the docs but then I noticed that the table does not really reflect the timezones in the source code. With the view it's now almost trivial to keep both synced but it still is a source of errors. Furthermore since nobody has complained about the incorrect table so far, I don't think many people care. And those who do can easily consult the view or the file of the Default set directly. Joachim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Time zone definitions to config files
David Fetter [EMAIL PROTECTED] writes: On Mon, Jul 24, 2006 at 11:59:34PM -0400, Tom Lane wrote: The documentation is still in need of help ... in particular, Table B-4 (timezone names) is now out of sync with reality. I'll take a whack at that patch this evening PDT or tomorrow evening at the latest. We're too late in the cycle to go over this, but maybe we can figure out a way to have this data read from the same data source as the pg_timezones VIEW does at compile time. Keeping two such table in synch seems error-prone. Well, the problem is exactly that there is no same data source anymore: the local DBA can customize the timezone list all he wants. We could document what the out-of-the-box settings are, but is it really useful to duplicate that info in the SGML docs? We don't for example provide a copy of psql \df+ output in the SGML docs, and I'm wondering if this isn't kind of the same animal. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] AT TIME ZONE
Hi, Just a quick check that the extension to AT TIME ZONE to allow specifying intervals as well as country/city is on the list for 8.1. I believe it was a fairly simple thing to do now that we have our own time zone library... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] AT TIME ZONE
Hi, Just a quick check that the extension to AT TIME ZONE to allow specifying intervals as well as country/city is on the list for 8.1. I believe it was a fairly simple thing to do now that we have our own time zone library... Yeah, this is on my personal hope to do for 8.1 list. At least the country/city part, haven't really thought about the other one. //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] AT TIME ZONE
Yeah, this is on my personal hope to do for 8.1 list. At least the country/city part, haven't really thought about the other one. One of the two forms already works...can't quite remember which... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] AT TIME ZONE
On Feb 8, 2005, at 20:43, Christopher Kings-Lynne wrote: Yeah, this is on my personal hope to do for 8.1 list. At least the country/city part, haven't really thought about the other one. One of the two forms already works...can't quite remember which... I think this is perhaps what you were trying to remember: http://archives.postgresql.org/pgsql-hackers/2004-10/msg00870.php Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] AT TIME ZONE
Added to TODO. --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: TODO entry? * Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken * allow customization of the known set of TZ names (generalize the present australian_timezones hack) I'm not sure whether we already have an entry for the latter. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] AT TIME ZONE
TODO entry? --- Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: With the new timezone stuff, is there any reason this shouldn't be made to work now in CVS: test=# select current_timestamp at time zone 'Australia/Perth'; ERROR: time zone australia/perth not recognized Lack of round tuits. We have to look at merging the hardwired zone names in the datetime token table with the zic timezone names. And somewhere in there the 'australian_timezones' GUC variable should vanish in favor of a locally-configurable list of recognized TZ names. But it's not happening for 8.0 ... this was stuff to be doing two months ago ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] AT TIME ZONE
Bruce Momjian [EMAIL PROTECTED] writes: TODO entry? * Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken * allow customization of the known set of TZ names (generalize the present australian_timezones hack) I'm not sure whether we already have an entry for the latter. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] AT TIME ZONE
Folks, I wonder whether this TODO item also covers cases such as inserting into a table where one field is time in the local timezone and the other is time in GMT. Not sure if such a thing is desirable or even possible (in the SQL standard). The syntax I'm imagining feels pretty awkward. I guess the select syntax would allow storing gmt in both fields but extracting one as local and the other as gmt. Shahbaz On Tue, 24 Aug 2004 10:46:23 -0400, Tom Lane [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: TODO entry? * Merge hardwired timezone names with the TZ database; allow either kind everywhere a TZ name is currently taken * allow customization of the known set of TZ names (generalize the present australian_timezones hack) I'm not sure whether we already have an entry for the latter. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Shahbaz Javeed ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] AT TIME ZONE
On Tue, Aug 24, 2004 at 03:16:30PM -0400, Shahbaz Javeed wrote: People, I wonder whether this TODO item also covers cases such as inserting into a table where one field is time in the local timezone and the other is time in GMT. Not sure if such a thing is desirable or even possible (in the SQL standard). The syntax I'm imagining feels pretty awkward. I wonder instead if it will be possible to store a timestamp without timezone in one field, and a timezone in another field. So I can get back a timestamp at the second-field timezone. I know this works for abbreviations, but this doesn't help me solve the problem at DST boundaries (abbreviations change at that point). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) The ability to monopolize a planet is insignificant next to the power of the source ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] AT TIME ZONE
Alvaro Herrera [EMAIL PROTECTED] writes: I wonder instead if it will be possible to store a timestamp without timezone in one field, and a timezone in another field. So I can get back a timestamp at the second-field timezone. f1 AT TIME ZONE f2 would be exactly the way to do that. I know this works for abbreviations, but this doesn't help me solve the problem at DST boundaries (abbreviations change at that point). Right, what we are talking about here is extending AT TIME ZONE to accept full zic-database zone names. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] AT TIME ZONE
With the new timezone stuff, is there any reason this shouldn't be made to work now in CVS: test=# select current_timestamp at time zone 'Australia/Perth'; ERROR: time zone australia/perth not recognized Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] AT TIME ZONE
Christopher Kings-Lynne [EMAIL PROTECTED] writes: With the new timezone stuff, is there any reason this shouldn't be made to work now in CVS: test=# select current_timestamp at time zone 'Australia/Perth'; ERROR: time zone australia/perth not recognized Lack of round tuits. We have to look at merging the hardwired zone names in the datetime token table with the zic timezone names. And somewhere in there the 'australian_timezones' GUC variable should vanish in favor of a locally-configurable list of recognized TZ names. But it's not happening for 8.0 ... this was stuff to be doing two months ago ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] AT TIME ZONE bug in CVS?
What's with this? template1=# select current_timestamp(0); timestamptz 2002-08-21 16:39:40+08 (1 row) template1=# set time zone 'Australia/Sydney'; SET template1=# select current_timestamp(0); timestamptz 2002-08-21 18:39:49+10 (1 row) template1=# select current_timestamp(0) at time zone 'Australia/Sydney'; ERROR: Time zone 'australia/sydney' not recognized template1=# select current_timestamp(0) at time zone 'AEST'; timezone - 2002-08-21 18:40:07 (1 row) Shouldn't the textual version of the time zone work using 'at time zone' as well as 'set time zone'? And also, why does the column name change from timestamptz to timezone? Anyway, shouldn't it in fact be current_timestamp? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] AT TIME ZONE bug in CVS?
template1=# select current_timestamp(0) at time zone 'Australia/Sydney'; ERROR: Time zone 'australia/sydney' not recognized The input is done using an internal lookup, not your system's time zone database. Much faster; setting time zone variables for every input will be substantially slower (though I haven't measured how much, it will involve opening files etc etc). And also, why does the column name change from timestamptz to timezone? Anyway, shouldn't it in fact be current_timestamp? The feature is implemented as a function call to timezone(), which returns a string. If it stayed a timestamp or something like that the time zone can not be frozen through the formatting/output process. - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])