CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

How to grab the value for the previous hour when the data type is TIMESTAMP with TIMEZONE

So I have some logic that will try to grab the Value (VALUE) correlated to the previous hour if the criteria are met. The HOUR column is a TIMESTAMP with TIME ZONE column. I figured I can run the following query but got an ORA-00932 inconsistent datatypes: expected TIMESTAMP WITH TIME ZONE got NUMBER error. Is there some sort of conversion function I have to add to my 'timestamp with timezone' value?

Below is my query code:

SELECT MAX(VALUE)
  FROM VALUE V
 WHERE CODE = 'HI'
   AND HR = '15-JAN-17 05.00.00.000000000 AM' - (1/24);

Thanks in advance.

Upvotes: 0

Views: 278

Answers (2)

D-Shih
D-Shih

Reputation: 46219

use TO_TIMESTAMP to let '15-JAN-17 05.00.00.000000000 AM' to datetime then minus one hour.

SELECT MAX(VALUE)
  FROM VALUE V
 WHERE CODE = 'HI'
   AND HR =  TO_TIMESTAMP('15-JAN-17 05.00.00.000000000 AM','DD-MON-RR HH.MI.SS.FF AM') - (1/24);

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191275

'15-JAN-17 05.00.00.000000000 AM' is a string, not a timestamp. You can convert it to a timestamp (with no time zone) as @D-Shih suggested, but you should specify the format mask and the date language rather than relying on NLS settings:

AND HR = to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
 'NLS_DATE_LANGUAGE=ENGLISH') - (1/24);

or if it's a fixed value (presumably it isn't, or you could just change that literal):

AND HR = timestamp '2017-01-15 05:00:00' - (1/24);

Subtracting a number of days from a timestamp gives you a date result, so you perhaps really want to do:

AND HR = timestamp '2017-01-15 05:00:00' - interval '1' hour;

This now stays as a timestamp, but you have no time zone information. If you know the time zone you can include it in string literal and format mask, or in the timestamp literal, e.g.:

AND HR = timestamp '2017-01-15 05:00:00 America/Los_Angeles' - (1/24);

or from your original string, if that's all you have to work with, you can use from_tz():

AND HR = from_tz(to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
  'NLS_DATE_LANGUAGE=ENGLISH'), 'America/Los_Angeles') - interval '1' hour;

Doing the interval subtraction last should mean that it handles DST properly.

Demo of the various conversions, starting from your string value:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF1 TZR TZD';

select
  to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
    'NLS_DATE_LANGUAGE=ENGLISH') as a_timestamp,
  to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
    'NLS_DATE_LANGUAGE=ENGLISH') - (1/24) as b_date,
  to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
    'NLS_DATE_LANGUAGE=ENGLISH') - interval '1' hour as c_timestamp,
  from_tz(to_timestamp('15-JAN-17 05.00.00.000000000 AM', 'DD-MON-RR HH.MI.SS.FF AM',
  'NLS_DATE_LANGUAGE=ENGLISH'), 'America/Los_Angeles') - interval '1' hour as d_timestamp_tz
from dual;

A_TIMESTAMP           B_DATE              C_TIMESTAMP           D_TIMESTAMP_TZ                               
--------------------- ------------------- --------------------- ---------------------------------------------
2017-01-15 05:00:00.0 2017-01-15 04:00:00 2017-01-15 04:00:00.0 2017-01-15 04:00:00.0 AMERICA/LOS_ANGELES PST

Upvotes: 2

Related Questions