Hello,
I have notice that in a table that has a timestamp(6) column the data is inserted as GMT date
I have checked the date values :
select SESSIONTIMEZONE,
CURRENT_DATE,
CURRENT_TIMESTAMP,
LOCALTIMESTAMP ,
SYSDATE,
SYSTIMESTAMP,
systimestamp at time zone 'GMT' NOW_AT_GMT
from dual;
+03:00
21/07/2020 20:37:54 <<<---- This is the current time
21/07/2020 20:37:54.943281 +03:00
21/07/2020 20:37:54.943281
21/07/2020 20:37:54
21/07/2020 20:37:54.943276 +03:00
21/07/2020 17:37:54.943276 +00:00 <<<--- This is the time that enter into a timestamp(6) column
And then queried the table . As you can see the filed value of time_created is 3 hour "in the past"
select ae.id, to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') curr_date,
ae.time_created
from event.all_events ae
where rownum < 3
id curr_date time_created
------ ------------------- ------------------------
1 21/07/2020 20:57:58 21/07/2020 17:57:39.000000
2 21/07/2020 20:57:58 21/07/2020 17:57:39.000000
I need to display the time_created with the "real" date and not as GMT
I can do it this way , but i dont like the solution
select ae.id, to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') curr_date,
ae.time_created,
CAST(ae.TIME_CREATED AS DATE) +3/24 time_created_after_convert
from event.all_events ae
where rownum < 3
id curr_date time_created time_created_after_convert
------ ------------------- ------------------------ ------------------------
1 21/07/2020 21:06:06 21/07/2020 18:05:59.000000 21/07/2020 21:05:59
2 21/07/2020 21:06:06 21/07/2020 18:05:59.000000 21/07/2020 21:05:59
I dont want to change the query above manually due to Daylight Saving Time .
Is there a way to replace the 3/24 (or 2/24 in the winter) by using TZ_OFFSET or something else .
Thanks
Yoav