Link to home
Start Free TrialLog in
Avatar of Mark_Co
Mark_CoFlag for United States of America

asked on

Question about timestamp and extract

SELECT EXTRACT(HOUR FROM LOCALTIMESTAMP) AS CORRECT
      ,EXTRACT(HOUR FROM SYSTIMESTAMP) AS INCORRECT
      ,SYSTIMESTAMP
      ,CURRENT_TIMESTAMP
      ,LOCALTIMESTAMP
      ,LOCALTIMESTAMP(0) NO_MS -- also noteworthy, precision of these functions is customizable */
FROM DUAL;

Open in new window


'Incorrect' returns 16 and I don't know why. Column 'Correct' returns 8 because it is 8AM Here in CA. Can someone explain where '16' would be coming from from the 'incorrect' column?
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark_Co

ASKER

So I could be receiving the time zone time back from England? That makes sense
Avatar of Mark_Co

ASKER

Thanks
You're welcome, and for proof of concept:


SQL> select to_char(systimestamp, 'hh24') HOUR,
  2         to_char(systimestamp, 'tzh') TMZ
  3  from dual;
Avatar of Sean Stuber
Sean Stuber

It's not so much that the time zone is back in England but rather the HOUR extract uses whatever information is available to produce the most consistent result.


LOCALTIMESTAMP does not have a time zone offset, because it's already converted to your local time zone,  so at 8am in CA,  the hour is 8.

SYSTIMESTAMP has a time zone offset,
so If your server was in CA, then 8am in CA is 8 with -8 offset,  for hour 16
same with CURRENT_TIMESTAMP

since your server is in England then 8am in CA is hour 16 with 0 offset on the server, hence you still get hour 16


the server could be in Florida (US/Eastern, offset -5)  and you'd still get the same results
11am with  -5 offset  =  hour 16
Avatar of Mark_Co

ASKER

Can I ask you if i understand correctly? The 'tzh' is returning the difference in hours between ca and Greenwich? Is that what it is doing?
Avatar of Mark_Co

ASKER

Thank you both :). Very helpful info
Avatar of Mark_Co

ASKER

to_char(systimestamp, 'tzh') TMZ  does this return the hour difference between my location and my server's?
>>> The 'tzh' is returning the difference in hours between ca and Greenwich? Is that what it is doing?

yes
Avatar of Mark_Co

ASKER

Awesome. Thanks