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   |
+-------------+------------+----------------+----------+`

Why are you storing a timestamp as a string (or possibly number) instead of as a real timestamp; and why store the time zone (offset, not region - which will give you problems with DST) as a separate column, when you could combine both as a single timestamp with time zone column? — Alex Poole, Aug 16 '19 at 09:47
@AlexPoole I only have read access to the database , I'm not the one who created the system ,the above is not the complete structure of the table because it has 220 columns . — Osama Al-Banna, Aug 16 '19 at 09:51
See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions194.htm — Serg, Aug 16 '19 at 09:55

Alex Poole · Accepted Answer · 2019-08-16T10:35:12.397

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

I tried to apply the same solution above on other table but I'm getting error the query `select from_tz(to_timestamp(START_TIME, 'YYYYMMDDHH24MISS'), UTC_TIME_CODE_OFFSET) from TAPIN_201906@billingdb;` I get the below error from oracle ORA-01874: time zone hour must be between -12 and 14 ORA-02063: preceding line from PRMDB 01874. 00000 - "time zone hour must be between -12 and 14" *Cause: The time zone hour specified was not in the valid range. *Action: Specify a time zone hour between -12 and 14. — Osama Al-Banna, Aug 16 '19 at 11:15
@OsamaAl-Banna - sounds like one of the UTC_TIME_CODE_OFFSET values in that table isn't valid. You'll need to check - maybe select distinct to start with - and then decide what to do with the invalid ones. (This is why it's better to store things in the correct data type... *8-) — Alex Poole, Aug 16 '19 at 11:19
I really understand you dude ,I'm having hard time to create a proper query, because it's huge telecom system and I can't ask to change it from my side . — Osama Al-Banna, Aug 16 '19 at 11:23
can you tell me please where or how to access the oracle time zone region names for instance `asia/baghdad` I need the table that store whole world, it will be much better if I find a reference like this `region | timezone | country | country code`,because time zone value not enough because some countries have DST (day light saving) which causing mismatch for me, do you know source where I can the whole timezone and dst data that is compatible with oracle ? do you know any good reference in the internet ? — Osama Al-Banna, Aug 29 '19 at 19:22
@OsamaAl-Banna - you can get the names and abbreviations from the `v$timezone_names` view. There's a bit more info [in the docs](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html). I mentioned DST problems from storing just the offset in my first comment on your question *8-) — Alex Poole, Aug 29 '19 at 19:33
I'm new to the timezone problems haha, I realize the problem after one week, but even though the query was given more accuracy than the old one, I still need more accuracy ,that's why I'm trying to handle the DST issue , thanks for the `v$timezone_names` table , I think I need to create another table based on this one and two more columns,`country name`,`telephone code` and the DST problem will be solved . — Osama Al-Banna, Aug 29 '19 at 19:59

Serg · Answer 2 · 2019-08-16T12:15:26.273

1

Warning The solution works only for a couple of Oracle 11g releases, see comments. Leaving it here just FYI.

One more option (TO_TIMESTAMP(TIMESTAMP, 'YYYYMMDDHH24MISS') AT TIME ZONE TIMEZONE) AT TIME ZONE sessiontimezone

Demo

with yourTable (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
)
select (TO_TIMESTAMP(TIMESTAMP, 'YYYYMMDDHH24MISS') AT TIME ZONE TIMEZONE) AT TIME ZONE '+05:00' t2 
from yourTable;


T2
06-JUN-19 10.59.59.000000000 AM +05:00
07-JUN-19 07.12.59.000000000 AM +05:00
09-JUN-19 08.06.59.000000000 PM +05:00

edited Aug 16 '19 at 12:15

answered Aug 16 '19 at 10:16

Serg

22,285
5
21
48

1

I don't think that's quite right. The result of `to_timestamp()` has to be implicitly cast to timestamp with time zone, using the session time zone setting, as that is what `AT` expects; then that is converted to the TIMEZONE zone, then back to session zone. Which is the same as `cast(to_timestamp(TIMESTAMP, 'YYYYMMDDHH24MISS') as timestamp with time zone)`; which doesn't give you the expected time. – Alex Poole Aug 16 '19 at 10:25
@AlexPoole Added demo , `'135959', '+08:00' = > 10.59.59.000000000 AM +05:00` and so on. Looks like correct result. – Serg Aug 16 '19 at 10:39
My guess is `TIMESTAMP` datatype is timezone agnostic so the first `AT TIME ZONE` just marks the value with timezone without changing the value, and the next `AT TIME ZONE` shifts the value. – Serg Aug 16 '19 at 10:43
That.. is very interesting. That does what you show in 11.2.0.2 ([db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=346eb06afe0c780a829c09f28fcbb51f)). But in 11.2.0.4 and later versions it does what I said ([db<>fiddle at 18c](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=346eb06afe0c780a829c09f28fcbb51f)). So that looks like a bug in 11.2.0.2 maybe. What version did you test this against? – Alex Poole Aug 16 '19 at 10:56
Looks like [a known issue in 11.2.0.1 and 11.2.0.2](https://support.oracle.com/epmos/faces/DocContentDisplay?id=1354912.1). So my original comment still stands (phew!). – Alex Poole Aug 16 '19 at 11:03

Converting string timestamp to local-time zone in Oracle

2 Answers2