10 Replies Latest reply: Jul 24, 2012 8:29 AM by Christian Erlinger RSS

    how to get GMT or IST using PL/SQL FUNCTION

    Tarun.Oracle
      Hello I am using Oracle 10G database (OS: Windows-32 and Windows-64).
      How can i get the date and time GMT or International time without referring SYSDATE / DBDATE.
      Actual problem the DB-Sever date/time is frequently manipulated by end user and i want to log the actual date and time of transaction in some audit table.

      Regards,

      Tarun
        • 1. Re: how to get GMT or IST using PL/SQL FUNCTION
          Prabodh
          >
          Hello I am using Oracle 10G database (OS: Windows-32 and Windows-64).
          How can i get the date and time GMT or International time without referring SYSDATE / DBDATE.
          >
          This is not Oracle Forms related query. Please post in SQL and PL/SQL or Database General
          >
          Actual problem the DB-Sever date/time is frequently manipulated by end user and i want to log the actual date and time of transaction in some audit table.
          >
          Enduser changing DB server date-time is not at all a healthy situation. You need to stop this.

          Cheers.

          PS: Before doing anything else look up the Timestamp data type in documentation.

          Edited by: Prabodh on Jul 23, 2012 10:55 AM
          • 2. Re: how to get GMT or IST using PL/SQL FUNCTION
            Tarun.Oracle
            thanks for the reply.

            I have no problem if that can be done using d2k(Forms 6i 2 tire mode).

            And can you (please) give me the link of the proper forum where i can post this?

            Tarun

            Edited by: Tarun.Oracle on Jul 23, 2012 2:35 PM
            • 3. Re: how to get GMT or IST using PL/SQL FUNCTION
              Christian Erlinger
              I have no problem if that can be done using d2k(Forms 6i 2 tire mode).
              Leaving aside that I doubt implementing a audit on the client is a good idea; if your users are able to change the system date on your database server what would prevent them from changing their client time?

              So the question remains: why are your users able to change the system date on the database server?!? To be able to do this on windows they must have at least some administrator privilages on the database server, and it might be just me but this wouldn't make me much comfortable.
              In my humble opinion you have a lot of other problems if this is true, the sysdate problem should be the last of your concerns. Please tell us why your users should be able to change the system date of your database server so someone can tell you that this is absolutely unnecessary and better achieved by implementing $other_solution.

              cheers
              • 4. Re: how to get GMT or IST using PL/SQL FUNCTION
                Tarun.Oracle
                Thanks for the suggestion. But i stick to the primary question?
                How can i GET GMT or IST bypassing SYSDATE?

                and the situation is like this: I develop a small APP using D2K AND oracle 10G database.

                This is been used by individual SHOPS at the own database server( most of the cases high end PCs) and they obviously have full control on there own computer and it is not possible to stop them doing some blunder.

                From my side i can only check that if wrong transaction happen then is it for some bug in the app or because of the date change like thing?

                Hope i have explained the problem?

                Regards
                Tarun
                • 5. Re: how to get GMT or IST using PL/SQL FUNCTION
                  Christian Erlinger
                  I suppose you could call a webservice of a server you trust to get a correct timestamp in your desired timezone. But not only would that cause some overhead as you'd need to do a HTTP Request each time you audit something which most certainly will slow your app down, you'd also need to make sure your customers server are able to access this webservice which sometimes surely will cause problems.
                  So even though I suggested you this solution I for myself wouldn't use it ;).

                  The only thing a sane server admin would tolerate changing the server time on a regular basis is a standard timesync. If memory serves a timesync is logged in the system event log on windows. A manual correction of the time is also recorded in the system event log.

                  So what you can do is to use sysdate nevertheless the server admin could have changed it. The first thing to do when searching for time related problems would be to check the system event log to see if they fiddled around with the system time.

                  cheers
                  • 6. Re: how to get GMT or IST using PL/SQL FUNCTION
                    Tarun.Oracle
                    Thanks for you valuable suggestion.

                    Now consider two thing:

                    1>The DB server have a internet connection on.
                    2> the system overload is not a concern for time being.

                    Now, is it possible to get the time information from any server hosted on internet.
                    Useing SQL, PL/SQL, Java or any thing else but the end out put i should get through a Oracle10g DATABASE function or
                    in a text file from where i can read it back in oracle.

                    Regards,

                    Tarun
                    • 7. Re: how to get GMT or IST using PL/SQL FUNCTION
                      Prabodh
                      Hi,

                      The sysdate is derived from the OS time. Its one and the same thing. So what will be achieve by getting the OS time when it is the same a sysdate?
                      It is not exactly easy to get the OS time , at the minimum you will need a component running on each of the DB servers and a requestor that keeps polling time from the DB servers periodically.

                      Cheers,
                      • 8. Re: how to get GMT or IST using PL/SQL FUNCTION
                        Christian Erlinger
                        It won't increase your server load, you'd simply have a lot of http wait events and the performance of your application would depend on your customers internet connection. Something not very desireable if you ask me, and most certainly will cause you headaches. Really bad headaches, and one day someone will ask who came up with the brilliant idea to get the system timestamp via http instead of...+sysdate+ (I smell a http://thedailywtf.com/ article coming up).

                        Anyway; here it goes: the http based sysdate function. I am using mod_plsql for my "webservice" running on a server from where I trust the date is correct. You can use anything you like, for a "real" webservice, the implementation will be a little bit different than that. Google would know the answer on how to call webservices from the database.
                        $[CHE_TEST@asterix1_impl] create or replace procedure server_time as
                          2  begin
                          3  htp.print(to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss'));
                          4  end;
                          5  /
                        
                        Procedure created.
                        As you can see the procedure does nothing else then outputting the sysdate of my database server in a specified date format. Then, on a different server I call the mod_plsql procedure via utl_http:
                        $[CHE_TEST@asterix2_al32utf8] create or replace function get_server_time return date is
                          2  begin
                          3    --default would be 60, this is clearly too much
                          4    utl_http.set_transfer_timeout(1);
                          5    return to_date(utl_http.request('http://ias4/asterix1_impl_cronet/server_time'), 'dd.mm.yyyy hh24:mi:ss');
                          6  exception
                          7    when utl_http.REQUEST_FAILED then
                          8      return sysdate;
                          9  end;
                         10
                         11  /
                        
                        Function created.
                        Now get_server_time will give me the time of my first database server (except the HTTP Request fails for some reason, then it'll be sysdate)
                        $[CHE_TEST@asterix2_al32utf8] select get_server_time from dual;
                        
                        GET_SERVER_TIME
                        -------------------
                        24.07.2012 13:35:00
                        Again if you are going down that road use the function carefully and not in every end of your application as it will give you performance problems.

                        cheers
                        • 9. Re: how to get GMT or IST using PL/SQL FUNCTION
                          Tarun.Oracle
                          Christian
                          Thanks a lot. You have not only taken care the technical portion but also analyze the implementation problem that might occur. I must keep all those points in mind before taking any attempt to implementing this idea. But I ensure you that I shall not attempt the get the time from the remote server each time. I have a different procedure ready to take care that. Only I need the get the actual time information in a periodic (say 30min) basis.

                          Thanks and regards,
                          Tarun
                          • 10. Re: how to get GMT or IST using PL/SQL FUNCTION
                            Christian Erlinger
                            You might want to change the value of set_transfer_timeout depending on the internet connection or the function will always return sysdate as the http request fails.

                            cheers