Re: [SQL] TIME ZONE SQL
Hi Richard, Follwing are the Results that I get Lets say I am in IST (Indian standart time) 15:00:00 hrs so equivalent time at US Mountain (-7:00) is 02:30:00 hrs and equivalent time at Japan(+9:00) is 18:30:00hrs NOW WHAT I have is this I have following fields in my table "customer_events" a) time_difference (which has values like +09:00 , -7:00, +00:00 etc) b) start_time (has value like 11:00:00 , 10:00:00 etc) c) send_before_time (has value like 00:15:00 , 00:07:00 etc) select ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN (start_time::time - send_before_time::time) and start_time::time) as yesno, current_time(0), CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference), (start_time::time - send_before_time::time) as difference, time_difference from customer_events WHEN I run "between" query like ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN (start_time::time - send_before_time::time) and start_time::time) it returns True (YES) when time_difference value are like +5:30 +5:00 i.e. works fine for positive values but failes for negative values i.e. -7:00, -6:00 time Zone values and returns me FALSE. So as per upper example lets say for an Japanese event "start_time=18:34:00" and "send_before_time="00:05:00" my above "between" query return true as current_time at japan zone (18:30) lies between thtat but for US Mountain event at "start_time=02:34:00" and "send_before_time=00:05:00" above "between" query return FALSE and current_time at US mountain zone (02:30) lies between that I don't know WHY Pls help.. in this. Also pls let me know if you need any other information. With Regards, Raman Garg -- Raman - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Raman" <[EMAIL PROTECTED]>; "pgsql-sql" <[EMAIL PROTECTED]> Sent: Thursday, February 05, 2004 1:33 AM Subject: Re: [SQL] TIME ZONE SQL > On Wednesday 04 February 2004 17:57, Raman wrote: > > > > This query runs fine when i have > > time_difference value like +5:30 +5:00 i.e. works fine for positive values > > but failes for negative values i.e. -7:00, -6:00 etc > > > > I don't know WHY WHY... pls help > > I am helpless. > > Can you give example outputs? It's difficult to decide otherwise. > -- > Richard Huxton > Archonet Ltd > ---(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] TIME ZONE SQL
On Thursday 05 February 2004 08:28, Raman wrote: > Hi Richard, > Follwing are the Results that I get > WHEN I run "between" query like > > ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN > (start_time::time - send_before_time::time) > and start_time::time) I think the issue is the "send_before_time" - I think this should be an interval rather than a time. I'm assuming it means something like "send warning message X hours before ..." Using the SQL below (your test data might need different values): CREATE TABLE tztest ( id serial, time_difference interval, start_timetime, send_before_time time, PRIMARY KEY (id) ); COPY tztest(time_difference,start_time,send_before_time) FROM stdin; -03 01:00 00:45 -03 02:00 00:45 -03 03:00 00:45 -03 04:00 00:45 -03 05:00 00:45 -03 06:00 00:45 -03 07:00 00:45 -03 08:00 00:45 -03 09:00 00:45 -03 10:00 00:45 -03 11:00 00:45 -03 12:00 00:45 -03 13:00 00:45 -03 14:00 00:45 -03 15:00 00:45 -03 16:00 00:45 -03 17:00 00:45 -03 18:00 00:45 -03 19:00 00:45 -03 20:00 00:45 -03 21:00 00:45 \. select ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN (start_time::time - send_before_time::time) and start_time::time) as yesno, current_time(0) AS curr_tm, CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS curr_with_timediff, (start_time::time - send_before_time::time) as start_pt, start_time AS end_pt, time_difference from tztest; -- Notice how we use send_before_time as an interval here -- select ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN (start_time::time - send_before_time::interval) and start_time::time) as yesno, current_time(0) AS curr_tm, CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS curr_with_timediff, (start_time::time - send_before_time::interval) as start_pt, start_time AS end_pt, time_difference from tztest; -- END SQL -- Gives the following results: richardh=# \i timezone_test.sql yesno | curr_tm | curr_with_timediff | start_pt | end_pt | time_difference ---+-++--+--+- f | 10:54:29+00 | 07:54:29-03| 00:15| 01:00:00 | -03:00 f | 10:54:29+00 | 07:54:29-03| 01:15| 02:00:00 | -03:00 f | 10:54:29+00 | 07:54:29-03| 02:15| 03:00:00 | -03:00 ...etc... f | 10:54:29+00 | 07:54:29-03| 20:15| 21:00:00 | -03:00 (21 rows) yesno | curr_tm | curr_with_timediff | start_pt | end_pt | time_difference ---+-++--+--+- f | 10:54:29+00 | 07:54:29-03| 00:15:00 | 01:00:00 | -03:00 ...etc... f | 10:54:29+00 | 07:54:29-03| 09:15:00 | 10:00:00 | -03:00 t | 10:54:29+00 | 07:54:29-03| 10:15:00 | 11:00:00 | -03:00 f | 10:54:29+00 | 07:54:29-03| 11:15:00 | 12:00:00 | -03:00 ...etc... f | 10:54:29+00 | 07:54:29-03| 20:15:00 | 21:00:00 | -03:00 (21 rows) Notice the difference between start_pt in each case. In the first example, time - time = difference, wheras in the second time - difference = time Does that help out at your end? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] TIME ZONE SQL
Hi Richard,
What I am having is
CREATE TABLE "customer_events" (
"event_id" numeric (10) NOT NULL,
"customer_id" numeric (10) NOT NULL,
"event_name" varchar (100) ,
"event_datetime" date ,
"start_time" time ,
"repeat_untill_date" date ,
"send_before_time" time,
"time_difference" time
PRIMARY KEY ("event_id"))
So my "send_before_time" is of datatype "time" only as you have assumed.
only difference was, In your table you are having time_difference field as
"interval" type.
I am subtracting time field from a time field. i.e. start_time::time -
send_before_time::time
so creating (start_time::time - send_before_time::time) as start_pt is not
making any difference at my side.
(I hope I am getting your point what you have explained)
Anyways i got a solution in this way..
Actually my "between" is creating some problems and is not giving me results
so what I have done is . IN MY WHERE CLAUSE OF QUERY:
Where ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time - send_before_time::time)
and start_time::time)
OR
((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
(start_time::time and (start_time::time - send_before_time::time))
--> check the difference two between
now one of my results work for positive time zone(+5:30) and another for
negative time zone(-7:00)
Well, it worked for me now.. maybe some logic of neagative time zone is
there due to which our time calculation make the difference of two time
greater. :-?
Thanks for your descriptive and nice explanation...
Regards,
-- Raman
- Original Message -
From: "Richard Huxton" <[EMAIL PROTECTED]>
To: "Raman" <[EMAIL PROTECTED]>; "pgsql-sql"
<[EMAIL PROTECTED]>
Sent: Thursday, February 05, 2004 4:30 PM
Subject: Re: [SQL] TIME ZONE SQL
> On Thursday 05 February 2004 08:28, Raman wrote:
> > Hi Richard,
> > Follwing are the Results that I get
>
> > WHEN I run "between" query like
> >
> > ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> > (start_time::time - send_before_time::time)
> > and start_time::time)
>
> I think the issue is the "send_before_time" - I think this should be an
> interval rather than a time. I'm assuming it means something like "send
> warning message X hours before ..."
>
> Using the SQL below (your test data might need different values):
>
> CREATE TABLE tztest (
> id serial,
> time_difference interval,
> start_timetime,
> send_before_time time,
> PRIMARY KEY (id)
> );
>
> COPY tztest(time_difference,start_time,send_before_time) FROM stdin;
> -03 01:00 00:45
> -03 02:00 00:45
> -03 03:00 00:45
> -03 04:00 00:45
> -03 05:00 00:45
> -03 06:00 00:45
> -03 07:00 00:45
> -03 08:00 00:45
> -03 09:00 00:45
> -03 10:00 00:45
> -03 11:00 00:45
> -03 12:00 00:45
> -03 13:00 00:45
> -03 14:00 00:45
> -03 15:00 00:45
> -03 16:00 00:45
> -03 17:00 00:45
> -03 18:00 00:45
> -03 19:00 00:45
> -03 20:00 00:45
> -03 21:00 00:45
> \.
>
>
> select
> ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> (start_time::time - send_before_time::time)
> and start_time::time) as yesno,
>
> current_time(0) AS curr_tm,
> CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS
> curr_with_timediff,
>
> (start_time::time - send_before_time::time) as start_pt,
> start_time AS end_pt,
> time_difference
> from
> tztest;
>
> -- Notice how we use send_before_time as an interval here
> --
> select
> ((CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference)) BETWEEN
> (start_time::time - send_before_time::interval)
> and start_time::time) as yesno,
>
> current_time(0) AS curr_tm,
> CURRENT_TIME(0) AT TIME ZONE "interval" (time_difference) AS
> curr_with_timediff,
>
> (start_time::time - send_before_time::interval) as start_pt,
> start_time AS end_pt,
> time_difference
> from
> tztest;
>
> -- END SQL --
>
> Gives the following results:
> richardh=# \i timezone_test.sql
> yesno | curr_tm | curr_with_timediff | start_pt | end_pt |
> time_difference
> ---+-++--+--+-
> f | 10:54:29+00 | 07:54:29-03| 00:15| 01:00:00 | -03:00
> f | 10:54:29+00 | 07:54:29-03| 01:15| 02:00:00 | -03:00
> f | 10:54:29+00 | 07:54:29-03| 02:15| 03:00:00 | -03:00
> ...etc...
> f | 10:54:29+00 | 07:54:29-03| 20:15| 21:00:00 | -03:00
> (21 rows)
>
> yesno | curr_tm | curr_with_timediff | start_pt | end_pt |
> time_difference
> ---+-++--+--+-
> f | 10:54:29+00 | 07:54:29-03| 00:15:00 | 01:00:00 | -03:00
> ...etc...
> f | 10:54:29+00 | 07:54:29-03| 09:15:00 | 10:00:00 | -03:00
> t | 10:54:29+00 | 07:54:29-03| 10:15:00 | 11:00:00 | -03:00
> f | 10:54:29+00 | 07:54:29-03| 11:15:00 | 12:00:00 | -03:00
> ...etc...
> f | 10:54:29+00 | 07:54:29-03| 2
Re: [SQL] TIME ZONE SQL
On Thursday 05 February 2004 14:59, Raman Garg wrote:
> Hi Richard,
>
> What I am having is
>
> CREATE TABLE "customer_events" (
> "event_id" numeric (10) NOT NULL,
> "customer_id" numeric (10) NOT NULL,
> "event_name" varchar (100) ,
> "event_datetime" date ,
> "start_time" time ,
> "repeat_untill_date" date ,
> "send_before_time" time,
> "time_difference" time
> PRIMARY KEY ("event_id"))
>
> So my "send_before_time" is of datatype "time" only as you have assumed.
> only difference was, In your table you are having time_difference field as
> "interval" type.
>
> I am subtracting time field from a time field. i.e. start_time::time -
> send_before_time::time
> so creating (start_time::time - send_before_time::time) as start_pt is not
> making any difference at my side.
> (I hope I am getting your point what you have explained)
My point is that subtracting one time from another does not give you a time.
3:30pm (time) - 2:00pm (time) = 1h30m (interval)
If "send_before_time" is an interval (e.g. '1 hour and 30 minutes') you should
probably make it an interval.
If it is in fact a time (e.g. '2:00 pm') then it doesn't make sense to
subtract it.
Even if your solution works, I'd recommend getting your types right - it'll
save effort later.
--
Richard Huxton
Archonet Ltd
---(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
Re: [SQL] TIME ZONE SQL
"Raman Garg" <[EMAIL PROTECTED]> writes: > Actually my "between" is creating some problems and is not giving me results > so what I have done is . IN MY WHERE CLAUSE OF QUERY: I suspect that this revised clause will give you problems too, namely selecting rows you don't want. I think what may actually be happening here is that you have times wrapping around past midnight. For instance consider regression=# select '10:30'::time + '15 hours'::interval; ?column? -- 01:30:00 (1 row) regression=# select '10:30'::time - '15 hours'::interval; ?column? -- 19:30:00 (1 row) It seems to me that your approach to the problem is all wrong, and you need to be using timestamp-based calculations not time-of-day-based calculations. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Could not convert UTF-8 to ISO8859-1
I've noticed a difference in behavior between 7.2 and 7.3 with regards
to character recoding and I'm a little perplexed about how to work
around.
I have a database in LATIN-1 that is accessed read-write by a Java app.
Naturally, the Java code keeps all of its strings in UTF8 so when I
prepare a sql statement, someone is recoding these characters to
LATIN-1 for me.
In 7.2, if the Unicode string contained a character that wasn't valid
in the database encoding (LATIN-1) either pgsql or the jdbc driver (I'm
not really sure which) would silently convert these characters to
question marks.
In 7.3, the same string will throw a "Could not convert UTF-8 to
ISO8859-1" error.
I can work around this by doing the following hack in Java:
String s = "some unicode string";
byte[] tmp = s.getBytes("latin1");
s = new String(tmp, 0, tmp.length, "latin1");
But I'm sure there is a better way to do this.
Any suggestions?
cva
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Slow sub-selects, max and count(*)
Thanks Josh and Ian, narrowing the problem down. The really slow line is the one where I try and remove potential duplicates. It does not look at the indexes. Point on using copy rather than insert is taken. I use copy to load to a temp file so I can test the data and alter certain values. I suppose I could: copy to temp / alter/ save/ copy into fsechist but that is rather ugly and as it is not the insert that is the real bug bear I would like to fix this first. Ian you suggested a simular problem was solved using "exists" rather than "in". I am not sure how the use differs. I have tried to include it in option 4 below. Trial Solutions: 1) delete from fsechist where hist_q in (select hist_q from fsechist, temp_shareprices where hist_tick = ticker and dte = hist_date); far too slow 2) update fsechist set hist_tick = \'@@\' from fsechist h, temp_shareprices t where t.ticker = h.hist_tick and h.hist_date = t.dte; delete from fsechist where hist_tick = \'@@\'; really , really slow. Thought this would use the indexes but "explain" ( being my friend) still gives the query plan as a sequential scan 3) -- does not allow insert of a single company datadelete from fsechist where hist_date in (select distinct dte from temp_shareprices); Works quick enough but has limited functionality. ( does not cope with imports other than a full end of trading) 4) delete from fsechist where exists(select 1 from fsechist, temp_shareprices where hist_tick = ticker and dte = hist_date); Tried and still took over 60 secs before I cancelled the request. Indexes Both tables have indexes defined for tick and date. tick and date of the same data type in both cases.
Re: [SQL] Slow sub-selects, max and count(*)
On Thu, Feb 05, 2004 at 15:53:08 +0800, Richard Sydney-Smith <[EMAIL PROTECTED]> wrote: > Trial Solutions: > > 1) delete from fsechist where hist_q in (select hist_q from fsechist, > temp_shareprices where hist_tick = ticker and dte = hist_date); Don't you want: delete from fsechist where hist_q in (select hist_q from temp_shareprices where hist_tick = ticker and dte = hist_date); You should be able to refer to the table being deleted instead of joining to it again in the subselect. As long as you are using 7.4.x, this should be pretty fast. > > far too slow Since this is slower than the exists solutions, my guess is that you are using a version prior to 7.4. > > 2) > > update fsechist set hist_tick = \'@@\' from fsechist h, temp_shareprices t where > t.ticker = h.hist_tick and h.hist_date = t.dte; > delete from fsechist where hist_tick = \'@@\'; > > really , really slow. Thought this would use the indexes but "explain" ( being my > friend) still gives the query plan as a sequential scan Again don't add fsechist to the from list. This joins with fsechist twice. > > 3) > > -- does not allow insert of a single company data > delete from fsechist where hist_date in (select distinct dte from temp_shareprices); > > Works quick enough but has limited functionality. ( does not cope with imports other > than a full end of trading) I think using the distinct clause is going to slow this down, especially if you are using a version prior to 7.4 since a sort is used instead of a hash table to get unique values. > > 4) > > delete from fsechist where exists(select 1 from fsechist, temp_shareprices where > hist_tick = ticker and dte = hist_date); > > Tried and still took over 60 secs before I cancelled the request. > Indexes Again, drop fsechist from the exists subselect. > > Both tables have indexes defined for tick and date. tick and date of the same data > type in both cases. You can also do a join using the nonstandard implied from syntax. You can do something like: delete from fsechist where hist_tick = temp_shareprices.ticker and temp_shareprices.dte = hist_date; ---(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] Slow sub-selects, max and count(*)
Richard, > Ian you suggested a simular problem was solved using "exists" rather than "in". I am not sure how the use differs. I have tried to include it in option 4 below. Hmmm ... this piece of advice is dated; with PG 7.4, IN() queries should be plenty fast. If you're using 7.3 or less, though watch out! > 1) delete from fsechist where hist_q in (select hist_q from fsechist, temp_shareprices where hist_tick = ticker and dte = hist_date); I can't help you with this unless you attribute column names to their tables. Please use full table.column syntax. > update fsechist set hist_tick = \'@@\' from fsechist h, temp_shareprices t where t.ticker = h.hist_tick and h.hist_date = t.dte; Put an ANALYZE fsechist here. > delete from fsechist where hist_tick = \'@@\'; Is there a reason why you are doing this in two steps? > -- does not allow insert of a single company data > delete from fsechist where hist_date in (select distinct dte from temp_shareprices); The DISTINCT is completely unnecessary. > delete from fsechist where exists(select 1 from fsechist, temp_shareprices where hist_tick = ticker and dte = hist_date); This is not the same query as #1. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] unsubscribe
---(end of broadcast)--- TIP 8: explain analyze is your friend
