Difference of 1 hour wile using sub string or to_char
Neha Gupta, June 20, 2019 - 1:54 pm UTC
when we try to run the below script :
with rws as (
SELECT (SELECT FROM_TZ(CAST(sysdate AS TIMESTAMP), 'Europe/Paris') AT TIME ZONE
'AFRICA/CASABLANCA' from dual)sysd from dual
)
select sysd from rws;
we get the result as
20-JUN-19 02.51.37.000000000 PM AFRICA/CASABLANCA
when i run the below query:
with rws as (
SELECT (SELECT FROM_TZ(CAST(sysdate AS TIMESTAMP), 'Europe/Paris') AT TIME ZONE
'AFRICA/CASABLANCA' from dual)sysd from dual
)
Select substr(sysd,1,18) from rws;
I get the results as:
20-JUN-19 01.51.16
even when i change the session timezone to 'AFRICA/CASABLANCA' i am still facing the same issue.
Is this some kind of oracle bug or is there something i am missing?
this is hugely affecting my project. kindly help
June 20, 2019 - 3:12 pm UTC
I can't reproduce this. Which makes it tricky to speculate as to the cause!
To help us diagnose, please share:
- What happens when you select sysd and substr ( sysd ) in the same query?
- What are your session's NLS settings? (select * from nls_session_parameters)
Also...
You mention "we" running the script getting the first output. But "I" (you) running the script getting the second.
Who exactly is running these scripts? From where? Do they both have the same NLS settings? Are they both run against the same database?
One hour diffrence in result
Neha Gupta, June 24, 2019 - 11:36 am UTC
The NLS Parameters are as below:
Parameter Value
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
When I try to execute the request in the same query
with rws as (
SELECT (SELECT FROM_TZ(CAST(sysdate AS TIMESTAMP), 'Europe/Paris') AT TIME ZONE 'AFRICA/CASABLANCA' from dual)sysd from dual
)
select sysd,substr(sysd,1,18) from rws;
result:
SYSD SUBSTR(SYSD,1,18)
24-JUN-19 10.52.53.000000000 AM AFRICA/CASABLANCA
24-JUN-19 09.52.53
--> Still showing a difference of 1 hour
there is a batch that runs these scripts which runs on version Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production of oracle.
it has the same NLS format.
I tries altering the session time zone but still the same result.
Kindly suggest
June 24, 2019 - 12:49 pm UTC
I still can't reproduce this...
Can you use systimestamp instead of sysdate? What effect does this have?
Output using systimestamp instead of sysdate
Neha Gupta, June 25, 2019 - 9:49 am UTC
the output is still the same
July 11, 2019 - 3:04 pm UTC
I can't reproduce this. You're going to have to take this up with support.
implicit conversion is evil
A reader, July 11, 2019 - 6:58 pm UTC
@NEHA
what do you expect as a result when doing substr on a timestamp with time zone ?
the engine is forced to do some implicit conversion which is evil.
why on earth do you want to do substr on something other than a string ?
July 12, 2019 - 8:55 am UTC
why on earth do you want to do substr on something other than a string ?
Good question...