X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 65782D1DA2E for ; Thu, 5 Feb 2004 08:32:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 68995-01 for ; Thu, 5 Feb 2004 04:32:25 -0400 (AST) Received: from smartdatainc.com (unknown [203.129.220.82]) by svr1.postgresql.org (Postfix) with ESMTP id 67C21D1D90E for ; Thu, 5 Feb 2004 04:32:14 -0400 (AST) Received: from raman ([192.168.199.212]) by smartdatainc.com ([192.168.199.92]) with SMTP (MDaemon.PRO.v6.5.2.R) for ; Thu, 05 Feb 2004 14:02:18 +0530 Message-ID: <006101c3ebc2$134c0dc0$d4c7a8c0@raman> From: "Raman" To: "Richard Huxton" , "pgsql-sql" References: <200402040928.08456.josh@agliodbs.com> <018c01c3eb48$593da250$d4c7a8c0@raman> <200402042003.07189.dev@archonet.com> Subject: Re: TIME ZONE SQL Date: Thu, 5 Feb 2004 13:58:43 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-MDRemoteIP: 192.168.199.212 X-Return-Path: ramang@smartdatainc.com X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/52 X-Sequence-Number: 16743 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" To: "Raman" ; "pgsql-sql" 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 >