[SQL] Query TIME ZONE
Hi All, I have a query regarding Time Zone Interval QUERY: "select current_time AT TIME ZONE INTERVAL '+5:30'" it is running fine BUT When I take the value of interval from DB table like: "select current_time AT TIME ZONE INTERVAL time_difference from customer_events" here : time_difference - is my varchar column in the table customer_events. This gives me errors " parse error at or near "time_difference" at character 43 " I tried to type cast it time_difference::text etc but it in vain. Pls help me ... its realy urgent. Thanks in advance Raman Garg
[SQL] TIME ZONE SQL
Hi Pls see this Query I have following fields in my table "customer_events" a) time_difference (which has values like -05:00 , +05:30, +00:00 etc) b) start_time (has value like 11:05, 10:00 etc) c) send_before_time (has value like 00:05, 00:10 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 MY PROBLEM: 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. do reply back Regards, Raman Garg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
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])
[SQL] Date format issue
Hello All... In my query of time zone i have details of interval with me like '-9:00', '+5:30' etc my problem is regarding the format of Date which i receive... ie. when I add the interval field the results are like: Query: select current_date at TIME ZONE "interval" '+5:30'; timezone 11399908 years 8 mons 1538 days but instead of interval when i run query like" Query: select current_date at TIME ZONE 'IST' timezone 2004-03-17 20:30:00 I want the format should be like 2004-03-17 20:30:00 How can I do that with INTERVAL information with me?? pls guide me.. Also can I have link to some doucmentation where i can read about Date/Time zone related theroy/functions?? thanks in advance.. regards, Raman Garg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
hi, I am new of postgre sql.im using cursors in pgsql and getting tuples successfully returns in c++, but how can i raise exception in the stored function?.pl anyone help me. This is my function : CREATE or REPLACE FUNCTION sp_test_validatePartnerCode(text) RETURNS int4 AS ' DECLARE t_p_code ALIAS FOR $1; curPartner CURSOR FOR SELECT partner_code FROM test_license WHERE partner_code = t_p_code; BEGIN OPEN curPartner; return curPartner; END; 'LANGUAGE 'plpgsql' ; advance thanks , sundar __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Query TIME ZONE
Thanks Tom it worked for me... yes I got it wrong.. thanks for the correction. thanks a lot.. -- Raman - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Raman" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, January 28, 2004 11:28 AM Subject: Re: [SQL] Query TIME ZONE > "Raman" <[EMAIL PROTECTED]> writes: > > [ okay: ] > > "select current_time AT TIME ZONE INTERVAL '+5:30'" > > [ not okay: ] > > "select current_time AT TIME ZONE INTERVAL time_difference from customer_events" > > "TIME ZONE INTERVAL" is not a SQL construct. You have misunderstood the > interaction of two different SQL constructs: > timestamp AT TIME ZONE timezonespec > INTERVAL 'interval-literal' > One of the possible forms of "timezonespec" in the AT TIME ZONE operator > is an interval value, so your first example works fine. Your second > example does not work because the INTERVAL 'foo' construct is only for > simple literal constants. > > > here : time_difference - is my varchar column in the table customer_events. > > Why are you using varchar rather than an interval column? An interval > value would work directly in this construct and would provide some > checking that entered values are sane. > > If you are absolutely intent on using varchar as the column datatype, > you can do a run-time cast like this: > select current_time AT TIME ZONE "interval"(time_difference) from customer_events > but don't complain when the query fails because some rows contain > time_difference strings that don't look like legal interval values... > > regards, tom lane > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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 |
