Solved

Oracle SQL Sysdate Time Zone Problem

Posted on 2009-07-16
10
1,130 Views
Last Modified: 2013-12-19
I have a table order. It has two columns apmt_date and apmt_time_zone.
we are storing time zones like P, M, C, E and empty. TIme Zones must be appended ny T at last.
That means P = PT, C = CT etc.

Now i have query like below
select * from order where apmt_date > sysdate.

Iam having problem here as iam not checking with time zones. I dont know the time zone of that oracle db instance.

I want a query which will also take care of time zones.

Please help,
Rave
0
Comment
10 Comments
 
LVL 6

Expert Comment

by:tangchunfeng
ID: 24868119
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch4datetime.htm
0
 
LVL 7

Expert Comment

by:grzessio
ID: 24868157
you could youse oracle new_time statement, but i do not know how you could convert your time zones to oracle time zones:
select * from V$TIMEZONE_NAMES;


your query:
select * from order where new_time(apmt_date, 'from_time_zone', 'to_time_zone') > sysdate

Open in new window

0
 
LVL 48

Accepted Solution

by:
schwertner earned 500 total points
ID: 24868344
Check this and make it workable. Fix it with hours of the timezones.

SELECT CASE when apmt_time_zone = '' OR 'E' then apmt_date
                     when apmt_time_zone =  'C' then apmt_date + 1/24
                     when apmt_time_zone =   'M' then apmt_date + 2/24
                     when apmt_time_zone =   'P' then apmt_date + 3/24
              END
FROM order;

After that try this:

select * from order
where CASE when apmt_time_zone = '' OR 'E' then apmt_date
                     when apmt_time_zone =  'C' then apmt_date + 1/24
                     when apmt_time_zone =   'M' then apmt_date + 2/24
                     when apmt_time_zone =   'P' then apmt_date + 3/24
              END
> sysdate
               
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 74

Expert Comment

by:sdstuber
ID: 24868913
to extract your database's current timezone offset  you can use EXTRACT

select extract(timezone_hour from systimestamp) from dual
0
 

Author Comment

by:Ravi Kiran Reddy Katkuri
ID: 24878509
select  hdr.apmt_dt, hdr.apmt_time_zone,
        decode(APMT_TIME_ZONE,'CT',apmt_dt,
                              'ET',apmt_dt - 1/24,
                              'MT',apmt_dt + 1/24,
                              'PT',apmt_dt + 2/24,
                              apmt_dt)
from        uop_ord_hdr hdr

The above query will give the desired result.
Any how a lot for all of your inputs.
Thanks a lot,
Rave
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24878551
The author's proposed solution is only a minor variation from a previously posted solution
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24878571
Points are not everything in life.
The main thing is that the asker is satisfied by his answer.
I have no objections.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question