Get time based on time zone

Arvind Singh

10y1.1kOracle

i am fetching date and time in oracle using below query

time zone is UK

SELECT FROM_TZ(sys_extract_utc(systimestamp),'UTC') AT TIME ZONE ( '+00:00' ) FROM DUAL;

but i am getting wrong time. plz help

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Jadav10y ago
HI Arvind,
I had a small example for it.

Converts date to users time zone according to given id.
Date time object to be converted.
Zone id. ie. UTC.
Returns converted datetime object.
public static System.DateTime ConvertDateToUserTimeZone(System.DateTime UserDate, string ZoneID)
           {
               try
               {
                   UserDate = UserDate.ToUniversalTime();
                   TimeZoneInfo TimeInfo = TimeZoneInfo.FindSystemTimeZoneById(ZoneID);
                   DateTime UserTime = TimeZoneInfo.ConvertTimeFromUtc(UserDate, TimeInfo);
                   return UserTime;
               }
               catch
               {
                   return UserDate;
               }
           }
Try this.
I it a working one.
Hope it helps.
Thanks.
Nitin Sontakke10y ago
The time you are getting is perfect.
Please do not get confused between GMT and UK time, I am saying this because many people do it. I am not sure about you.
The Greenwich is in London but it's time is not the UK time. This is because UK is now following day light saving timing. For details refer to the following link:
http://www.timeanddate.com/time/change/uk/london?year=2016
As such the GMT and UK time is identical only in Winter when Day Light Saving is OFF.
Furthermore the GMT off-set of India is +5:30 and not +5:00.
Arvind Singh10y ago
Hi Nitin Sontakke
I simply mentioned that it showing wrong time means if current time is 9:00 AM it is giving 8:00 AM thats it.
if you did not get it please dont type to make your comment.
Thansk
Nitin Sontakke10y ago
The question is "what do you mean by wrong?"
Could you please paste here the query result?
Also, please include to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') in the select in addition to what you are selecting.
There is no point in commenting unless you show the result.
Arvind Singh10y ago
HiNitin Sontakke
'+00:00' means UK time zone, it is giving wrong for that.
also if i set other time zone +05:00 India it is also giving wrong.
Thanks,
Nitin Sontakke10y ago
Please let us know what makes you think that it is a wrong time?
Where are you based?
Please provide your location (or GMT off-set) and the UTC time you are getting.