• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

Converting 'timestamp with time zone' into 'timestamp' datatype..

i have data with 'TIMESTAMP(6) WITH TIME ZONE' ...
2 example values:
02/10/2011 12:05:05.000000 AM -05:00
05/17/2011 03:20:19.000000 AM -04:00
can you give a query to convert this into TIMESTAMP datatype after adding the TZR value..?
0
Rao_S
Asked:
Rao_S
  • 3
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
What is the desired output?
0
 
sdstuberCommented:
if you're just trying to get the GMT time


CAST(SYSTIMESTAMP AT TIME ZONE 'GMT' AS TIMESTAMP)
0
 
Rao_SAuthor Commented:
thanks sdstuber, that is what i what looking for....
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
if you "really" wanted to do the offset math yourself you could do it like this...



   CAST(SYSTIMESTAMP AS TIMESTAMP)
       - NUMTODSINTERVAL(EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP), 'hour')
       - NUMTODSINTERVAL(EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP), 'minute')


but,  I recommend using the "AT TIME ZONE" method, simpler and more efficient.

as above,  use your own timestamp value for systimestamp
0
 
Rao_SAuthor Commented:
thank you...i like the 'at time zone' also.....!...
0
 
Rao_SAuthor Commented:
thank you...
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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