Re: [SQL] Timezone issue with date_part
Ken Kennedy <[EMAIL PROTECTED]> writes:
> [ date_part('epoch') is wrong for a timestamp value ]
The epoch value is really only correct for a TIMESTAMP WITH TIME ZONE
value. If you apply date_part('epoch') to a timestamp without time zone,
as you appear to be doing here, what you will get is the epoch for the
given value interpreted as GMT.
A hack solution is to cast the value to TIMESTAMP WITH TIME ZONE before
extracting the epoch; the cast will assume that the given value is local
time. But a better idea is to store the column as TIMESTAMP WITH TIME
ZONE in the first place.
(IMHO, the SQL spec is really brain-dead to define timestamp without
time zone as the default form of timestamp; the variant with time zone
is much more useful for most applications. It's far too easy to shoot
yourself in the foot when working with zoneless timestamps --- usually
in a way that you won't notice until daylight-savings transition time
comes around, or you roll out the app to users in other time zones.)
regards, tom lane
---(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: [SQL] Different size in the DATA directory
On Fri, Nov 01, 2002 at 10:48:00PM -0500, Bruce Momjian wrote: > Tim, I guess your problem is dead index pages that can't be reclaimed, > and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a > /contrib/reindexdb script in 7.3. As I see, contrib/reindexdb requires perl for commandline procesing. I don't think it's a good idea, since e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. Thomas, why not to use sed? -- Fduch M. Pravking ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Timezone issue with date_part
On Sat, Nov 02, 2002 at 09:17:14AM -0500, Tom Lane wrote:
> Ken Kennedy <[EMAIL PROTECTED]> writes:
> > [ date_part('epoch') is wrong for a timestamp value ]
>
> The epoch value is really only correct for a TIMESTAMP WITH TIME ZONE
> value. If you apply date_part('epoch') to a timestamp without time zone,
> as you appear to be doing here, what you will get is the epoch for the
> given value interpreted as GMT.
Excellent! I see. The table is indeed using TIMESTAMP WITHOUT TIME
ZONE. (It is, in fact, an old 'datetime' hold-on in the table creation
DDL.) Hopefully, I can alter that sucker in place...it'll help for
upgrade scripts.
> A hack solution is to cast the value to TIMESTAMP WITH TIME ZONE before
> extracting the epoch; the cast will assume that the given value is local
> time. But a better idea is to store the column as TIMESTAMP WITH TIME
> ZONE in the first place.
Gotcha. I've confirmed the hack solution is working for now, and
eliminates my even hackier (more hackish?) two-call solution. I'll get
with the package owner (this is in an OpenACS package) and we'll work
out an upgrade for the table and procs.
> (IMHO, the SQL spec is really brain-dead to define timestamp without
> time zone as the default form of timestamp; the variant with time zone
> is much more useful for most applications.
I see exactly what you're saying now. I guess that's the reason
datetime resolves to 'TIMESTAMP WITHOUT TIME ZONE'? I agree...the TZ
is very useful to have tagging along!
Thanks so much for your help, Tom!
--
Ken Kennedy | http://www.kenzoid.com| [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Timezone issue with date_part
Tom, > (IMHO, the SQL spec is really brain-dead to define timestamp without > time zone as the default form of timestamp; the variant with time zone > is much more useful for most applications. It's far too easy to shoot > yourself in the foot when working with zoneless timestamps --- usually > in a way that you won't notice until daylight-savings transition time > comes around, or you roll out the app to users in other time zones.) It's pretty easy to shoot yourself in the foot with time zones, as well. For example, most people are thrown off by the daylight-savings-time shift in date calculations; for example: select '2002-10-20 00:00:00 PDT'::TIMESTAMPTZ + '2 weeks'::INTERVAL jwnet-> ; ?column? 2002-11-02 23:00:00-08 This sort of behavior can really muck with calendar applications. Of course, it could be solved with a DAY/WEEK subtype, but I've already advocated for that. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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: [SQL] Different size in the DATA directory
Alexander M. Pravking wrote: > On Fri, Nov 01, 2002 at 10:48:00PM -0500, Bruce Momjian wrote: > > Tim, I guess your problem is dead index pages that can't be reclaimed, > > and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a > > /contrib/reindexdb script in 7.3. > > As I see, contrib/reindexdb requires perl for commandline > procesing. I don't think it's a good idea, since > e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. > > Thomas, why not to use sed? No perl? I am no perl guy, but I assumed everyone had that already. I just looked at the code, and yes, it should use sed rather than perl, especially since it is using it just for processing command line args. Seems it is a problem/bug for you. Patch applied to use sed rather than perl. -- 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 Index: contrib/reindexdb/reindexdb === RCS file: /cvsroot/pgsql-server/contrib/reindexdb/reindexdb,v retrieving revision 1.2 diff -c -c -r1.2 reindexdb *** contrib/reindexdb/reindexdb 18 Oct 2002 18:41:20 - 1.2 --- contrib/reindexdb/reindexdb 3 Nov 2002 01:19:21 - *** *** 111,118 dbname="$2" shift ;; ! -d*|--dbname=*) ! dbname=`echo $1 | perl -pn -e 's/^--?d(bname=)?//'` ;; # Reindex specific Table. Disables index reindexing. --- 111,121 dbname="$2" shift ;; ! -d*) ! dbname=`echo "$1" | sed 's/^-d/'` ! ;; ! --dbname=*) ! dbname=`echo "$1" | sed 's/^--dbname=//'` ;; # Reindex specific Table. Disables index reindexing. *** *** 120,127 table="$2" shift ;; ! -t*|--table=*) ! table=`echo $1 | perl -pn -e 's/^--?t(able=)?//'` ;; # Reindex specific index. Disables table reindexing. --- 123,133 table="$2" shift ;; ! -t*) ! table=`echo "$1" | sed 's/^-t//'` ! ;; ! --table=*) ! table=`echo "$1" | sed 's/^--table=//'` ;; # Reindex specific index. Disables table reindexing. *** *** 129,136 index="$2" shift ;; ! -i*|--index=*) ! index=`echo $1 | perl -pn -e 's/^--?i(ndex=)?//'` ;; # Yeah, no options? Whine, and show usage. --- 135,145 index="$2" shift ;; ! -i*) ! index=`echo "$1" | sed 's/^-i//'` ! ;; ! --index=*) ! index=`echo "$1" | sed 's/^--index=//'` ;; # Yeah, no options? Whine, and show usage. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
