GMT Time Stamp [message #663286] |
Sun, 28 May 2017 15:37 |
|
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 #663293 is a reply to message #663286] |
Mon, 29 May 2017 00:38 |
|
Michel Cadot
Messages: 68665 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 #663334 is a reply to message #663333] |
Tue, 30 May 2017 10:08 |
cookiemonster
Messages: 13925 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 #663356 is a reply to message #663335] |
Wed, 31 May 2017 06:49 |
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 #664257 is a reply to message #664241] |
Mon, 10 July 2017 07:21 |
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 |
|
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 |
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 #664270 is a reply to message #664269] |
Mon, 10 July 2017 12:05 |
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
|
|
|
|