Skip to Main Content
  • Questions
  • Substr, to_char gives a difference of 1 hour from actual time

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, NEHA.

Asked: June 12, 2019 - 7:33 am UTC

Answered by: Chris Saxon - Last updated: July 12, 2019 - 8:55 am UTC

Category: SQL Developer - Version: Version 4.2.0.17.089

Viewed 1000+ times

You Asked

Hello,

1) When we execute below query :

with rws as (
  (SELECT (FROM_TZ(CAST(sysdate AS TIMESTAMP), 'Europe/Paris') AT TIME ZONE
 (select nvl(timezone,'Europe/Paris') date1 from dim.nloqt_top_sit_d where sit = 'KE' )) sysd   FROM DUAL)
)Select sysd from rws;  

Result :   
12-JUN-19 08.26.33.000000000 AM AFRICA/CASABLANCA 


2) When we add Substr, to_char or to_date function to sysd, we get 1 hour difference in result.

with rws as (
  (SELECT (FROM_TZ(CAST(sysdate AS TIMESTAMP), 'Europe/Paris') AT TIME ZONE
 (select nvl(timezone,'Europe/Paris') date1 from dim.nloqt_top_sit_d where sit = 'KE' )) sysd   FROM DUAL)
)
select substr(sysd,1,18) from rws;  

Result: 
12-JUN-19 07.29.12


Can anyone explain why we are getting one hour difference in the result?

and we said...

Sorry, I can't reproduce the substr effect:

alter session set nls_timestamp_tz_format = 'DD-MON-YY HH24:MI:SS TZR';

with rws as (
  select from_tz (cast (sysdate as timestamp),'Europe/Paris') 
           at time zone 'Europe/Paris' sysd1,
         from_tz (cast (sysdate as timestamp),'Europe/Paris') 
           at time zone 'AFRICA/CASABLANCA' sysd2
  from   dual
)
select sysd1, substr(sysd1,1,18), 
       sysd2, substr(sysd2,1,18)
from   rws;

SYSD1                             SUBSTR(SYSD1,1,18)   SYSD2                                  SUBSTR(SYSD2,1,18)   
13-JUN-19 15:41:13 EUROPE/PARIS   13-JUN-19 15:41:13   13-JUN-19 14:41:13 AFRICA/CASABLANCA   13-JUN-19 14:41:13    


My guess: Paris and Casablanca are one hour apart. So the queries return dates in different time zones for some reason.

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Difference of 1 hour wile using sub string or to_char

June 20, 2019 - 1:54 pm UTC

Reviewer: Neha Gupta from India

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
Chris Saxon

Followup  

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

June 24, 2019 - 11:36 am UTC

Reviewer: Neha Gupta from India

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
Chris Saxon

Followup  

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

June 25, 2019 - 9:49 am UTC

Reviewer: Neha Gupta from India

the output is still the same
Chris Saxon

Followup  

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

July 11, 2019 - 6:58 pm UTC

Reviewer: A reader

@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 ?
Chris Saxon

Followup  

July 12, 2019 - 8:55 am UTC

why on earth do you want to do substr on something other than a string ?

Good question...

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.