asked on

create view - specify timezone for a date-type of column

i have an oracle table-mytable1 with a column mydatecolumn1 - it is of the type - date
I want to create a view out of mytable1...

When i create the view, can i specify the timezone for mydatecolumn1, which specifies that all the date-time values of mydatecolumn1 are according to the timezone - USA Newyork Time or EST....

Please help

thanks so much

David VanZandt

My untried opinion is no, the view cannot add attributes to the source datatypes. However, your view may be able to convert it with a function. Let me read a little.

ts84zs

ASKER

i can use sql-functions to specify convert(or do something with mydatecolumn1) mydatecolumn1 and specify timezone in that function

please help is there any such sql function ?

thansk so much

ASKER CERTIFIED SOLUTION

David VanZandt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ts84zs

ASKER

thanks so much where can i find timezone-values that goes in new_time(entry_date, 'EST', 'CST'),
like CST, PST, EST, UTC

I have to convert it to UTC timezone

David VanZandt

Addl: to represent date then time with a T, see https://www.experts-exchange.com/questions/23024216/Converting-Oracle-dates-to-UTC-format.html.

Also see for the conversion: http://blog.watashii.com/2009/11/oracle-timezone-conversions-gmt-to-localtime/

SELECT CAST((FROM_TZ(CAST(TO_DATE('1999-12-01 22:00:00',
'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), SESSIONTIMEZONE)
AT TIME ZONE 'GMT') AS DATE)
FROM DUAL;