Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

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
0
ts84zs
Asked:
ts84zs
  • 3
  • 2
1 Solution
 
DavidSenior Oracle Database AdministratorCommented:
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.
0
 
ts84zsAuthor Commented:
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
0
 
DavidSenior Oracle Database AdministratorCommented:
Have a look at:
"Switching Time Zones

The function new_time is used to convert a time to different time zones. To illustrate this we’ll look at entry 5 from the dates file.

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 02:30 PM

This database is in US Eastern time but we want to display the time in US Central.

SELECT entry, to_char(new_time(entry_date, 'EST', 'CST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 01:30 PM"

Read the source at: http://www.lifeaftercoffee.com/2005/09/22/converting-time-zones-in-oracle/

So, converting to EST -- how would you want to pass the original time zone into the function?  I presume it varies.
0
 
ts84zsAuthor Commented:
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
0
 
DavidSenior Oracle Database AdministratorCommented:
Addl:  to represent date then time with a T, see http://www.experts-exchange.com/Database/Oracle/Q_23024216.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;
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now