2

In below, why isn't Daylight savings included in to_char()? How can i get the time with daylight savings?

SELECT systimestamp AT TIME ZONE 'Australia/Adelaide' from dual;
SELECT TO_CHAR(systimestamp AT TIME ZONE 'Australia/Adelaide' ,'yyyy-MM-dd HH:MI:SS AM') from dual;

returns

16-OCT-13 07.19.01.165681000 PM AUSTRALIA/ADELAIDE
2013-10-16 06:19:01 PM
user503647
  • 41
  • 1
  • 1
  • 5

2 Answers2

2
select to_char( systimestamp at time zone 'AUSTRALIA/ADELAIDE', 
'HH24:MI:SS TZR TZD' ) from dual

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510

Yván Ecarri
  • 1,661
  • 18
  • 39
  • still the time returned doesnt include daylight savings. it has one hour less compared to systimestamp at time zone 'AUSTRALIA/ADELAIDE' – user503647 Oct 16 '13 at 10:22
  • @user503647, it looks OK to me- compare the [sqlfiddle demo](http://www.sqlfiddle.com/#!4/d41d8/18718) to [this clock](http://wwp.greenwichmeantime.com/time-zone/australia/south-australia/adelaide/) – A.B.Cade Oct 16 '13 at 13:14
  • You are right. Sqlfiddle (oracle 11g R2) returns correct time with daylight savings. I am running it in oracle sql developer against oracle 10g (10.2.0.4) database. Am i missing any database configuration? – user503647 Oct 17 '13 at 05:03
1

The issue was because of outdated timezones files in oracle server. Adelaide timezones were updated around 2008 or 2007. once timezone files were updated issue got resolved.

user503647
  • 41
  • 1
  • 1
  • 5