[SQL] Why do I get these results?????
Hi,
Why do I get the following result from the query below? I expected that,
given the fact that there are over 100 "Jansen" (but no "jansen") in
"Nijmegen" the first record would definitively be people living in
"Nijmegen". If I change the order to the order that is commented out,
the query goes OK.
SELECT
addresses.zipcode,
addresses.city,
addresses.housenumber,
addresses.housenumberdetails,
customers.lastname
FROM prototype.customers JOIN prototype.addresses ON
customers.contactaddress = addresses.objectid
WHERE
TRIM(UPPER(customers.lastname)) >= TRIM(UPPER('Jansen'))
AND
TRIM(UPPER(addresses.city)) >= TRIM(UPPER('NIJMEGEN'))
ORDER BY customers.lastname, addresses.city, addresses.zipcode
--ORDER BY addresses.city, customers.lastname, addresses.zipcode
limit 5
Result:
"3089TN";"ROTTERDAM";"5";"";"jansen"
"5712XG";"SOMEREN";"13";"";"jansen"
"6511PS";"NIJMEGEN";"23";"";"Jansen"
"6523RE";"NIJMEGEN";"13";"";"Jansen"
"6524NP";"NIJMEGEN";"37";"A";"Jansen"
TIA
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why do I get these results?????
Hi Frans
On Fri, 2006-03-03 at 10:06 +0100, Van Elsacker Frans wrote:
> Joost
>
> Why do you use
>
> AND TRIM(UPPER(addresses.city)) >= TRIM(UPPER('NIJMEGEN'))
> and not
> AND TRIM(UPPER(addresses.city)) = TRIM(UPPER('NIJMEGEN'))
>
> upper(Rotterdam) en upper(Someren) meets >= TRIM(UPPER('NIJMEGEN'))
Because that is what I want: I want everyone that is called "" and
lives in a city that is equal or larger than "". Jansen and Nijmegen
are just examples.
> commentline with order statements can not influence the content of the
> result
What do you mean by that?
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Sequential scan where Index scan expected (update)
On fim, 2006-03-02 at 23:28 -0800, Bryce Nesbitt wrote: > I'm getting sequential scans (and poor performance), on scans using my > primary keys. This is an older postgres. > Can anyone help figure out why? > > > demo=# \d xx_thing > Table "public.xx_thing" > Column |Type | Modifiers > -+-+--- > thing_id | bigint | not null ... > demo=# explain update xx_thing_event set thing_color='foo' where > thing_event_id=1; > QUERY PLAN > - > Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110) ... > demo=# select * from version(); > version > -- > PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 try one of: ... WHERE thing_event_id=1::bigint ... WHERE thing_event_id='1' or upgrade to >= 8.0 your problem is that the 1 in 'thing_event_id=1' is parsed as an integer but not a bigint, so the planner does not find a matching index. so you either have to scpecify ::bigint or ::int8 in the query or quote the number, which will cause postgres to cast it to the column's type. if you try ... WHERE thing_event_id=100 you will see the index used because this number cannot be a int4 so is parsed as a bigint. newer versions of Postgresql can deal with this. gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Sequential scan where Index scan expected (update)
On Thu, Mar 02, 2006 at 11:37:53PM -0800, Gregory S. Williamson wrote: > It seems unlikely but maybe try an explict cast for the thing_id call, e.g. > explain update xx_thing_event set thing_color='foo' where > thing_event_id=1::bigint; The server is pre-8.0 so it's likely that this is indeed the problem. Unfortunately the table that was shown in the \d output (xx_thing) isn't the same table as in the update command (xx_thing_event) so we can only guess that thing_event_id is a bigint based on what xx_thing looks like. > It may also be that 5842 rows is enough that the planner decides > it is faster to do a sequential scan that the busier index scan > (read index, get data row, versus just reading all the necessary > pages in one faster sequential scan). [...] > QUERY PLAN > - > Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110) >Filter: (thing_event_id = 1) > (2 rows) The 5842.48 figure isn't a row count, it's a cost estimate that includes the number of pages and rows in the file. The row count estimate is 1 row; if the cost estimate for a sequential scan is 5842.48 then an index scan to fetch one row would surely be faster. If thing_event_id is a bigint and has an index then casting the value to bigint should result in an index scan. Another way would be to quote the value: UPDATE xx_thing_event SET thing_color = 'foo' WHERE thing_event_id = '1'; -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Why do I get these results?????
On fös, 2006-03-03 at 09:50 +0100, Joost Kraaijeveld wrote:
> Hi,
>
> Why do I get the following result from the query below? I expected that,
> given the fact that there are over 100 "Jansen" (but no "jansen") in
> "Nijmegen" the first record would definitively be people living in
> "Nijmegen". If I change the order to the order that is commented out,
> the query goes OK.
>
> SELECT
> addresses.zipcode,
> addresses.city,
> addresses.housenumber,
> addresses.housenumberdetails,
> customers.lastname
> FROM prototype.customers JOIN prototype.addresses ON
> customers.contactaddress = addresses.objectid
> WHERE
> TRIM(UPPER(customers.lastname)) >= TRIM(UPPER('Jansen'))
> AND
> TRIM(UPPER(addresses.city)) >= TRIM(UPPER('NIJMEGEN'))
> ORDER BY customers.lastname, addresses.city, addresses.zipcode
> --ORDER BY addresses.city, customers.lastname, addresses.zipcode
> limit 5
>
> Result:
>
> "3089TN";"ROTTERDAM";"5";"";"jansen"
> "5712XG";"SOMEREN";"13";"";"jansen"
> "6511PS";"NIJMEGEN";"23";"";"Jansen"
> "6523RE";"NIJMEGEN";"13";"";"Jansen"
> "6524NP";"NIJMEGEN";"37";"A";"Jansen"
What LOCALE are you using?
Looks like it either sorts lowercase before uppercase
or treats them as equivalent.
Why do you not provide us with a simple test case?
Why involve a join and irrelevant columns?
What does a simple test like this do for you?:
test=# create table t (c text, n text);
CREATE TABLE
test=# insert into t values ('ROTTERDAM','jansen');
INSERT 33566780 1
test=# insert into t values ('SOMEREN','jansen');
INSERT 33566781 1
test=# insert into t values ('NIJMEGEN','Jansen');
INSERT 33566782 1
test=# insert into t values ('NIJMEGEN','Jansen');
INSERT 33566783 1
test=# insert into t values ('NIJMEGEN','Jansen');
INSERT 33566784 1
test=# select * from t ORDER BY n,c;
c | n
---+
NIJMEGEN | Jansen
NIJMEGEN | Jansen
NIJMEGEN | Jansen
ROTTERDAM | jansen
SOMEREN | jansen
(5 rows)
(this in in C locale)
gnari
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[SQL] newbie question
Dear users.. I have fastly created a table in a postgresql database.. some columns where edited by hand (columns A, B, C), and some others (columns D, E, F) have been calculated as a result of mathematical equation (where the factors are the A, B, C columns) now I simply need to change some values in the A, B, C columns and I would like to obtain the correct values in the D, E, F column... I know that this is a tipical problem of a spreadsheet but how can I solve it with a DBMS?? there a way to impose some constrain, also after the table is already filled with values?? I need only some suggestions to start.. then I can go on by myself!! thank you very much Ivan -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Why do I get these results?????
On Fri, 2006-03-03 at 09:51 +, Ragnar wrote: > Looks like it either sorts lowercase before uppercase > or treats them as equivalent. Ooops. I must sort resultset using the same condition is I select. Oops again. them. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] newbie question
ivan marchesini <[EMAIL PROTECTED]> schrieb: > Dear users.. > I have fastly created a table in a postgresql database.. > some columns where edited by hand (columns A, B, C), and some others > (columns D, E, F) have been calculated as a result of mathematical > equation (where the factors are the A, B, C columns) You should create a table with (a,b,c) and a view. Below a example. test=# create table foo (a int, b int, c int); CREATE TABLE test=# create view foo_view as (select a,b,c,a*b as ab, a*c as ac, b*c as bc from foo); CREATE VIEW > now I simply need to change some values in the A, B, C columns and I > would like to obtain the correct values in the D, E, F column... > I know that this is a tipical problem of a spreadsheet but how can I > solve it with a DBMS?? test=# insert into foo values (2,3,4); INSERT 0 1 test=# select * from foo_view ; a | b | c | ab | ac | bc ---+---+---+++ 2 | 3 | 4 | 6 | 8 | 12 (1 row) test=# update foo set a=3; UPDATE 1 test=# select * from foo_view ; a | b | c | ab | ac | bc ---+---+---+++ 3 | 3 | 4 | 9 | 12 | 12 (1 row) HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 1: 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] newbie question
On Fri, Mar 03, 2006 at 10:43:09AM +0100, ivan marchesini wrote: > I have fastly created a table in a postgresql database.. > some columns where edited by hand (columns A, B, C), and some others > (columns D, E, F) have been calculated as a result of mathematical > equation (where the factors are the A, B, C columns) > now I simply need to change some values in the A, B, C columns and I > would like to obtain the correct values in the D, E, F column... > I know that this is a tipical problem of a spreadsheet but how can I > solve it with a DBMS?? Use triggers or a view. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] newbie question
Thanks to all... another question... is it possible to copy a table to a view and then back the view to a table??? thank you very much... ivan Il giorno ven, 03/03/2006 alle 11.51 +0100, Andreas Kretschmer ha scritto: > ivan marchesini <[EMAIL PROTECTED]> schrieb: > > > Dear users.. > > I have fastly created a table in a postgresql database.. > > some columns where edited by hand (columns A, B, C), and some others > > (columns D, E, F) have been calculated as a result of mathematical > > equation (where the factors are the A, B, C columns) > > You should create a table with (a,b,c) and a view. Below a example. > > test=# create table foo (a int, b int, c int); > CREATE TABLE > > test=# create view foo_view as (select a,b,c,a*b as ab, a*c as ac, b*c as bc > from foo); > CREATE VIEW > > > > now I simply need to change some values in the A, B, C columns and I > > would like to obtain the correct values in the D, E, F column... > > I know that this is a tipical problem of a spreadsheet but how can I > > solve it with a DBMS?? > > test=# insert into foo values (2,3,4); > INSERT 0 1 > test=# select * from foo_view ; > a | b | c | ab | ac | bc > ---+---+---+++ > 2 | 3 | 4 | 6 | 8 | 12 > (1 row) > > test=# update foo set a=3; > UPDATE 1 > test=# select * from foo_view ; > a | b | c | ab | ac | bc > ---+---+---+++ > 3 | 3 | 4 | 9 | 12 | 12 > (1 row) > > > HTH, Andreas -- Ivan Marchesini Department of Civil and Environmental Engineering University of Perugia Via G. Duranti 93/a 06125 Perugia (Italy) e-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] tel: +39(0)755853760 fax: +39(0)755853756 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] newbie question
am 03.03.2006, um 12:30:20 +0100 mailte ivan marchesini folgendes: > Thanks to all... > another question... > is it possible to copy a table to a view and then back the view to a > table??? Yes this is possible. > > Il giorno ven, 03/03/2006 alle 11.51 +0100, Andreas Kretschmer ha please, no silly fullquote below the answer. HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Fri, Mar 03, 2006 at 12:19:22AM -, Simon Kinsella wrote: > Hi Andrew, > > I think I may have cracked this problem by combining a RULE ON DELETE which > calls a function instead of the standard DELETE op. No triggers. It was a Ah. Yes, likely. Yeah, you can't do that. A -- Andrew Sullivan | [EMAIL PROTECTED] 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 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] newbie question
On Fri, Mar 03, 2006 at 12:30:20PM +0100, ivan marchesini wrote: > another question... > is it possible to copy a table to a view and then back the view to a > table??? You need to read a basic textbook about what a view is. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Sequential scan where Index scan expected (update)
On Thu, Mar 02, 2006 at 11:28:49PM -0800, Bryce Nesbitt wrote: > Can anyone help figure out why? Well. . . > > > demo=# \d xx_thing > -+-+--- > thing_id | bigint | not null ^^ > demo=# explain update xx_thing_event set thing_color='foo' where ^^ . . .you haven't actually given us the right schema here, but if I had to guess, I'd say you could put this > thing_event_id=1; ^ in quotes. The automatic int4-int8 coercion is probably your problem. Also > -- > PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 ^ you need to upgrade PostgreSQL Right Now. There are serious problems with earlier 7.4 releases. Get the latest, or risk data corruption. A -- Andrew Sullivan | [EMAIL PROTECTED] 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
[SQL] how to make infinite intervals?
I see there is a isfinite(interval) function. So there must be something like a infinite interval. An infinite interval would be yuite handy for me at the moment. But i have no clue how to make one. interval 'infinity' does not do the trick. :) Thank you -- Tilman Baumann Software Developer Collax GmbH . Boetzinger Straße 60 . 79111 Freiburg . Germany p: +49 (0) 761-4514-836 f: +49 (0) 761-4563-793 ** Meet us at CeBIT ** LinuxPark, Halle 5, Stand E64/6 ---(end of broadcast)--- TIP 1: 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] how to make infinite intervals?
Tilman Baumann wrote: > I see there is a isfinite(interval) function. So there must be something > like a infinite interval. > > An infinite interval would be yuite handy for me at the moment. But i > have no clue how to make one. > > interval 'infinity' does not do the trick. :) On the TODO list: o Allow infinite dates and intervals just like infinite timestamps -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] how to make infinite intervals?
Bruce Momjian schrieb: Tilman Baumann wrote: I see there is a isfinite(interval) function. So there must be something like a infinite interval. An infinite interval would be yuite handy for me at the moment. But i have no clue how to make one. interval 'infinity' does not do the trick. :) On the TODO list: o Allow infinite dates and intervals just like infinite timestamps Oh, i see. Well, i can live without. :) Looking forward... -- Tilman Baumann Software Developer Collax GmbH . Boetzinger Straße 60 . 79111 Freiburg . Germany p: +49 (0) 761-4514-836 f: +49 (0) 761-4563-793 ** Meet us at CeBIT ** LinuxPark, Halle 5, Stand E64/6
Re: [SQL] how to make infinite intervals?
Tilman Baumann <[EMAIL PROTECTED]> writes:
> I see there is a isfinite(interval) function.
Looks like it's just a stub :-(
Datum
interval_finite(PG_FUNCTION_ARGS)
{
PG_RETURN_BOOL(true);
}
Evidently someone planned for infinite intervals a long time ago,
but never got round to it.
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Sequential scan where Index scan expected (update)
Andrew Sullivan wrote: thing_event_id=1; ^ in quotes. The automatic int4-int8 coercion is probably your problem. Also Yup that's it. But this project uses (ugh) Hibernate. I can't change it. I may have to change from BIGINT primary keys to INT. Also: Any hints on the table statistics? I turn them on, in a session, as user postgres, but get nothing: stage=# SHOW ALL; name | setting + ... stats_block_level | on stats_command_string | on stats_reset_on_server_start | on stats_row_level | on stats_start_collector | on stage=# select * from pg_statio_user_indexes; relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit --+++-+--+---+-- 18810975 | 18811183 | public | eg_invoice | ix22f7bc70c7de2059 | 0 | 0 18810979 | 18811184 | public | eg_order | ix522779518edf278d | 0 | 0 18810984 | 18811185 | public | eg_invoice_tax | ix2f10773c8edf278d | 0 | 0 18810986 | 18811186 | public | eg_order_line | ixf8331222783867cc | 0 | 0
Re: [SQL] Sequential scan where Index scan expected (update)
On Fri, Mar 03, 2006 at 11:35:55AM -0800, Bryce Nesbitt wrote: > Yup that's it. But this project uses (ugh) Hibernate. I can't change > it. I may have to change > from BIGINT primary keys to INT. > Well, you could upgrade from 7.4. > Also: > Any hints on the table statistics? I turn them on, in a session, as > user postgres, but get nothing: You have to SIGHUP to get those to take effect, IIRC. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] pg_dump and diffrent sizes
Hello pgsql-sql, I dump db with pg_dump v.8.1.3 on database postgresql server 7.4.7. Data directory with my db on pg 7.4.7 had 1,8GB and file with dump had 2,7GB. Database have blob fields. When I restore db on pg 8.1 - data directory have only 1GB, why? -- Best regards, Maciej mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg_dump and diffrent sizes
On Fri, 2006-03-03 at 14:10, Maciej Piekielniak wrote: > Hello pgsql-sql, > > I dump db with pg_dump v.8.1.3 on database postgresql server 7.4.7. > Data directory with my db on pg 7.4.7 had 1,8GB and > file with dump had 2,7GB. > Database have blob fields. > > When I restore db on pg 8.1 - data directory have only 1GB, why? I would guess that you have lots of highly compressible text entries, and postgresql is compressing them for you. The difference in size between 7.4 and 8.1 is likely due to unrecycled tuples in the data store. Even with regular vacuuming, some small percentage of dead tuples is normal. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Getting yyyy-mm-dd 00:00:00 in an arbitrary time zone
Title: Getting -mm-dd 00:00:00 in an arbitrary time zone
I am trying to find out what the last full day of data for an arbitrary timezone (generally not the pg client's timezone). I get the max(timestamp), then would like to remove the time portion. Sounded like a job for date_trunc, unfortunately date_trunc is not timezone aware:
select (date_trunc('day', '2006-01-31 23:00:00-800' at time zone 'CST')) at time zone 'CST'
2006-01-31 22:00:00-08
Since the client is in PST, the truncated date is returned as 2006-02-01 PST which is two hours before the desired time.
extract is slightly more promising:
select extract(DAY from '2005-12-31 23:00:00-800' at time zone 'CST')
1
This returns the correct day, so all I have to do is glue it back together in the right time zone:
select to_timestamp(extract(YEAR from '2005-12-31 23:00:00-800' at time zone 'CST')||'-'||
extract(MONTH from '2005-12-31 23:00:00-800' at time zone 'CST')||'-'||
extract(DAY from '2005-12-31 23:00:00-800' at time zone 'CST')||' 00:00:00 CST', '-MM-DD HH:MI:SS TZ')
ERROR: "TZ"/"tz" not supported
Has anyone solved this problem before?
Many thanks,
Robert
Select version()
PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
Re: [SQL] Getting yyyy-mm-dd 00:00:00 in an arbitrary time zone
"Davidson, Robert" <[EMAIL PROTECTED]> writes:
> I am trying to find out what the last full day of data for an arbitrary =
> timezone (generally not the pg client's timezone). I get the =
> max(timestamp), then would like to remove the time portion. Sounded like =
> a job for date_trunc, unfortunately date_trunc is not timezone aware:
> select (date_trunc('day', '2006-01-31 23:00:00-800' at time zone 'CST')) =
> at time zone 'CST'
> 2006-01-31 22:00:00-08
> Since the client is in PST, the truncated date is returned as 2006-02-01 =
> PST which is two hours before the desired time.
You're being quite unclear about what you want, but AFAICS the above
expression does exactly what you asked for. Namely, you get a time that
is midnight in the CST zone and then is adjusted to your current
timezone for display.
Perhaps you could be more clear about why you don't like this result?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Getting yyyy-mm-dd 00:00:00 in an arbitrary time zone
It does, doesn't it. And it does it just the way it is documented in 9.9.3 AT
TIME ZONE table 9.27.
I was expecting it to be harder and didn't see I had figured out the right
answer already - thanks!
Robert
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Friday, March 03, 2006 4:32 PM
To: Davidson, Robert
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Getting -mm-dd 00:00:00 in an arbitrary time zone
"Davidson, Robert" <[EMAIL PROTECTED]> writes:
> I am trying to find out what the last full day of data for an
> arbitrary = timezone (generally not the pg client's timezone). I get
> the = max(timestamp), then would like to remove the time portion.
> Sounded like = a job for date_trunc, unfortunately date_trunc is not timezone
> aware:
> select (date_trunc('day', '2006-01-31 23:00:00-800' at time zone
> 'CST')) = at time zone 'CST'
> 2006-01-31 22:00:00-08
> Since the client is in PST, the truncated date is returned as
> 2006-02-01 = PST which is two hours before the desired time.
You're being quite unclear about what you want, but AFAICS the above expression
does exactly what you asked for. Namely, you get a time that is midnight in
the CST zone and then is adjusted to your current timezone for display.
Perhaps you could be more clear about why you don't like this result?
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
