Converting string timestamp to local-time zone in Oracle

Question

I'm trying to sum data usage for mobile multiple mobile numbers world wide using oracle database,to achieve accurate result my SQL query need to to convert this string timestamp to my local time zone +03 ,for example the expected time for the first number 7803128475 after converted to +03 timezone should be 20190606085959

my current query is not accurate

select sum(data_usage) where timestamp between '20190601000000' and '20190630235959';

`+-------------+------------+----------------+----------+
| MSISDN      | DATA_USAGE | TIMESTAMP      | TIMEZONE |
+-------------+------------+----------------+----------+
| 7803128475  |   1223.323 | 20190606135959 | +08:00   |
| 78093678473 |   1323.323 | 20190607071259 | +05:00   |
| 79093648472 |   1423.323 | 20190609090659 | -06:00   |
+-------------+------------+----------------+----------+`

Answer 1

You can convert your string to a real time stamp with:

to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS')

You can then state the time zone that is in with:

from_tz(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS'), TIMEZONE)

Or you can combine the two string columns and convert both together:

to_timestamp_tz(TIMESTAMP || TIMEZONE, 'YYYYMMDDHH24MISSTZH:TZM')

(Your column names are confusing, so I've put them in uppercase to try to distinguish them more clearly...)

Either way, you can then convert that to your local time zone with:

from_tz(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS'), TIMEZONE) at local

or making it slightly clearer that it's using session time zone:

from_tz(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS'), TIMEZONE) at time zone sessiontimezone

or if you actually want the DB timezone:

from_tz(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS'), TIMEZONE) at time zone dbtimezone

With your example data as a CTE that gives you:

alter session set time_zone = 'Asia/Baghdad';

-- CTE for sample data
with your_table (MSISDN, DATA_USAGE, TIMESTAMP, TIMEZONE) as (
  select '7803128475',  1223.323, '20190606135959', '+08:00' from dual
  union all
  select '78093678473', 1323.323, '20190607071259', '+05:00' from dual
  union all
  select '79093648472', 1423.323, '20190609090659', '-06:00' from dual
)
-- example query
select MSISDN, DATA_USAGE, TIMESTAMP, TIMEZONE,
  to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS') as ts,
  from_tz(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS'), TIMEZONE) as tstz,
  from_tz(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS'), TIMEZONE) at local as local_tstz
from your_table;

MSISDN      DATA_USAGE TIMESTAMP      TIMEZO TS                    TSTZ                         LOCAL_TSTZ                        
----------- ---------- -------------- ------ --------------------- ---------------------------- ----------------------------------
7803128475    1223.323 20190606135959 +08:00 2019-06-06 13:59:59.0 2019-06-06 13:59:59.0 +08:00 2019-06-06 08:59:59.0 ASIA/BAGHDAD
78093678473   1323.323 20190607071259 +05:00 2019-06-07 07:12:59.0 2019-06-07 07:12:59.0 +05:00 2019-06-07 05:12:59.0 ASIA/BAGHDAD
79093648472   1423.323 20190609090659 -06:00 2019-06-09 09:06:59.0 2019-06-09 09:06:59.0 -06:00 2019-06-09 18:06:59.0 ASIA/BAGHDAD

If you are only going to use the converted time in the where-clause filter then you don't need to convert it to the local time zone at all, as long as you say what time zone you're comparing it with; and I woudl use >= and < instead of between:

select sum(DATA_USAGE)
from your_table
where from_tz(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS'), TIMEZONE)
  >= timestamp '2019-06-01 00:00:00 Asia/Baghdad'
and from_tz(to_timestamp(Timestamp, 'YYYYMMDDHH24MISS'), TIMEZONE)
  < timestamp '2019-07-01 00:00:00 Asia/Baghdad'

SUM(DATA_USAGE)
---------------
       3969.969