Home » SQL & PL/SQL » SQL & PL/SQL » GMT Time Stamp (PL SQL)
GMT Time Stamp [message #663286] Sun, 28 May 2017 15:37 Go to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
How do I display this time so my EST users can understand it?

20170524T132557.726 GMT varchar2(255)

Also, how do I perform calculations on it like this date - now?

Re: GMT Time Stamp [message #663288 is a reply to message #663286] Sun, 28 May 2017 16:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

It is a 100% design failure to store a TIMESTAMP value in a VARCHAR2 variable.

>Also, how do I perform calculations on it like this date - now?
convert string to TIMESTAMP with TimeZone

Re: GMT Time Stamp [message #663293 is a reply to message #663286] Mon, 29 May 2017 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select to_timestamp_tz('20170524T132557.726 GMT','YYYYMMDD"T"HH24MISS.FF3 TZR')
  2           at time zone 'EST' est_time
  3  from dual
  4  /
EST_TIME
---------------------------------------------------------------------------
24/05/2017 08:25:57.726 EST

1 row selected.

[Updated on: Mon, 29 May 2017 00:39]

Report message to a moderator

Re: GMT Time Stamp [message #663302 is a reply to message #663286] Mon, 29 May 2017 07:03 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
OldDog wrote on Sun, 28 May 2017 15:37
How do I display this time so my EST users can understand it?

20170524T132557.726 GMT varchar2(255)

Also, how do I perform calculations on it like this date - now?

I second BlackSwan's statement, "It is a 100% design failure to store a TIMESTAMP value in a VARCHAR2 variable."

See http://edstevensdba.com/oracle-data-types/understanding-oracle-date-formats/ for further discussion of why.
Re: GMT Time Stamp [message #663331 is a reply to message #663293] Tue, 30 May 2017 08:59 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
Thank you for your suggestion! this 'select to_timestamp' appears to be working but I think it is an hour off. How do I account for Daylight Savings time without having to change my report 2x/year?

Re: GMT Time Stamp [message #663332 is a reply to message #663331] Tue, 30 May 2017 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
would the result be correct if you used "EDT" instead of "EST"?
Re: GMT Time Stamp [message #663333 is a reply to message #663332] Tue, 30 May 2017 09:35 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
using 'EDT' I get ORA-01882 timezone region not found.
Re: GMT Time Stamp [message #663334 is a reply to message #663333] Tue, 30 May 2017 10:08 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to be more specific when specifying the timezone. Lots of places are in EST and they don't all go to EDT at the same time. But if you tell it where you are it'll work:
SQL> select to_timestamp_tz('20170101T132557.726 GMT','YYYYMMDD"T"HH24MISS.FF3 TZR') at time zone 'America/New_York' est_time from dual;
 
EST_TIME
--------------------------------------------------------------------------------
01-JAN-17 08.25.57.726000000 AMERICA/NEW_YORK
 
SQL> select to_timestamp_tz('20170601T132557.726 GMT','YYYYMMDD"T"HH24MISS.FF3 TZR') at time zone 'America/New_York' est_time from dual;
 
EST_TIME
--------------------------------------------------------------------------------
01-JUN-17 09.25.57.726000000 AMERICA/NEW_YORK
 
SQL> 
Re: GMT Time Stamp [message #663335 is a reply to message #663334] Tue, 30 May 2017 10:12 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
Perfect! Thank you.
Re: GMT Time Stamp [message #663356 is a reply to message #663335] Wed, 31 May 2017 06:49 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
just a note. You can see a list of all available legal time zones by running the following query.

select * from V$TIMEZONE_NAMES order by tzname;
Re: GMT Time Stamp [message #664239 is a reply to message #663293] Sun, 09 July 2017 17:20 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
I now have this statement that works in PL/SQL for a varchar field (that is really a GMT timestamp) that I needed to convert to Eastern Time.

(to_timestamp_tz(C.Corrsentdatetime,'YYYYMMDD"T"HH24MISS.FF3 TZR')
at time zone 'America/New_York') SENT_DATE

but when I try to run this as a pass thru query in MS Access, I get the following error:

[Oracle][ODBC][Ora]Driver not capable.(#3)

any thoughts?
Re: GMT Time Stamp [message #664241 is a reply to message #664239] Mon, 10 July 2017 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Driver information?
Code that get this error?
How can we reproduce what you have?

Re: GMT Time Stamp [message #664257 is a reply to message #664241] Mon, 10 July 2017 07:21 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
What version of Access are you using? What is the version of the database are you using? The message it returned is fairly clear. It's doesn't support the to_timestamp_tz call. However an easy way around it is to make a function on gers called (for example) to_tz and do the conversion in the function call instead of in the select.
Re: GMT Time Stamp [message #664263 is a reply to message #664241] Mon, 10 July 2017 10:13 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
I'm using Access 2007-10 with a "Microsoft ODBC for Oracle" driver. it is a reporting database and I am not sure what version it is. Here is a scaled down version of my query. I'm not sure what 'gers' are or how to work around this. Any suggestions would be greatly appreciated.
select c.workitemid case_number,
c.CORRSENTDATETIME OLD_sent_date,
(to_timestamp_tz(C.Corrsentdatetime,'YYYYMMDD"T"HH24MISS.FF3 TZR')
          at time zone 'America/New_York') SENT_DATE,
c.ACKNOWLEDGEMENTDATETIME akn_date,
c.corrfilename file_name, c.status
from MM_DATA_CORR c
where 
to_date(substr(CORRSENTDATETIME, 1, 8), 'yyyymmdd') >= trunc(sysdate)-7
and to_date(substr(CORRSENTDATETIME, 1, 8)||substr(CORRSENTDATETIME, 10, 4), 'yyyymmddhh24mi') < trunc(sysdate) + 18/24  



CM: added [code] tags, please do so yourself next time.

[Updated on: Mon, 10 July 2017 10:39] by Moderator

Report message to a moderator

Re: GMT Time Stamp [message #664267 is a reply to message #664263] Mon, 10 July 2017 11:30 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
create view olddog_v as
SELECT C.Workitemid Case_number,
       TO_DATE (SUBSTR (c.Corrsentdatetime, 1, 8) || SUBSTR (Corrsentdatetime, 10, 4),
           'yyyymmddhh24mi') Old_sent_date,
       (TO_TIMESTAMP_TZ (C.Corrsentdatetime, 'YYYYMMDD"T"HH24MISS.FF3 TZR')
            AT TIME ZONE 'America/New_York')
           Sent_date,
       C.Acknowledgementdatetime Akn_date,
       C.Corrfilename File_name,
       C.Status
  FROM Mm_data_corr C;

Then in your access program simply use the following query

select case_number,old_sent_date,sent_date,akn_date,file_name,status
  from olddog_v
  WHERE  trunc( old_sent_date)  >=  TRUNC (SYSDATE) - 7
  AND sent_date < TRUNC (SYSDATE) + 18 / 24
Re: GMT Time Stamp [message #664269 is a reply to message #664267] Mon, 10 July 2017 12:03 Go to previous messageGo to next message
OldDog
Messages: 10
Registered: May 2017
Junior Member
I cannot create a view in a production database. I am trying to run a pass thru query so that I may join this information to a table in a different database. I am looking for an alternative way to convert the GMT time stamp since this one is unsupported.


Re: GMT Time Stamp [message #664270 is a reply to message #664269] Mon, 10 July 2017 12:05 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
of course a developer can't create a view in a production database, but you can certainly ask the DBA or management to create the view. Let oracle to the heavy lifting for you
Re: GMT Time Stamp [message #664272 is a reply to message #664269] Mon, 10 July 2017 12:08 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you can use an Oracle driver instead of Microsoft one.

Previous Topic: Error ORA-01722: Invalid use of Number in Select with Xmlagg / Xmlelement
Next Topic: Update Match Flag Status in Invoice Distribution
Goto Forum:
  


Current Time: Fri Mar 29 02:48:03 CDT 2024