How to convert Unix timestamp to local date in Oracle?

Question

Internet, Please help!

I am using Oracle DB v 12c, where invoice issuing time is stored as unix timestamp in column of type NUMBER + there is also a VARCHAR2(128) column, which defines the timezone where the invoice was issued, eg 'Europe/London'.

So invoices issued on local date '2020-10-10' can be queried like this:

SELECT inv.invoiceID
FROM invoices inv
WHERE TO_CHAR(
                  FROM_TZ(timestamp '1970-01-01 00:00:00'
                              + NUMTODSINTERVAL(inv.INVOICETIME, 'SECOND'), 'UTC') AT TIME ZONE inv.timezoneName,
                  'YYYY-MM-DD') = '2020-10-10';

But unfortunately this query is not indexable.

The problem is, that this is not working:

CREATE INDEX Invoices_InvoiceDate ON Invoices (
        TO_CHAR(
                  FROM_TZ(timestamp '1970-01-01 00:00:00'
                              + NUMTODSINTERVAL(INVOICETIME, 'SECOND'), 'UTC') AT TIME ZONE timezoneName,
                  'YYYY-MM-DD')
                   );

Error: ORA-01743: only pure functions can be indexed

Because of using "AT TIME ZONE", what is "pure version" of this operator? Or in other words - how to property convert Unix timestamp to local date in Oracle?

https://stackoverflow.com/questions/37305135/oracle-convert-unix-epoch-time-to-date — OldProgrammer, Jun 25 '21 at 19:05
With the way you currently collect and store the data, I don't believe there is any "pure version" of the function, or "proper way to convert" from UTC to "local" date ("local" to the issuance of the invoice, that is; note that your non-standard use of the term is confusing readers, since the word "local" usually means "local to the database session", not "local to the time zone saved in the table"). — , Jun 26 '21 at 02:13
So: no "pure function", because to convert the dates, Oracle must consult the table of offsets for time zones. If a function must look things up in a table, it is automatically considered non-deterministic: data in the table may change over time, so the function will not always produce the same result. You and I may believe that this particular lookup table will never ever change, but the query parser isn't that detailed; the function uses a table lookup, so it's non-deterministic, non-indexable, period. So, you need to find a different way to store the input data, if you must have an index. — , Jun 26 '21 at 02:14
Thank you @mathguy for confirming that in this case function based index is not possible because of the timezones lookup table. So I'll probably solve it by storing the issue date in a separate column.v — Jansen, Jun 26 '21 at 05:26
For me it is not clear what you are looking for. Comparing Date/Times values with **strings** is usually not a smart choice. And using time zones on date values without any time does not make much sense either - at least it makes it more difficult to get your intention. I assume you are actually looking for this `WHERE TIMESTAMP '1970-01-01 00:00:00 UTC' + NUMTODSINTERVAL(INVOICETIME, 'SECOND') = FROM_TZ(TIMESTAMP '2020-10-10 00:00:00', timezoneName)`. — Wernfried Domscheit, Jun 26 '21 at 08:54
@WernfriedDomscheit, inv.INVOICETIME timestamp contains also the seconds, so exact condition would be `TIMESTAMP '1970-01-01 00:00:00 UTC' + NUMTODSINTERVAL(INVOICETIME, 'SECOND') BETWEEN FROM_TZ(TIMESTAMP '2020-10-10 00:00:00', timezoneName) AND FROM_TZ(TIMESTAMP '2020-10-10 23:59:59', timezoneName)`, but still it seems impossible to create an index which could be used by the constraint — Jansen, Jun 26 '21 at 10:16
This query should use the index which I proposed in my answer. What kind of "constraint" do you mean? — Wernfried Domscheit, Jun 26 '21 at 11:59
It can't use the index and needs to access full table data, because the filtering constraint `= FROM_TZ(TIMESTAMP '2020-10-10 00:00:00', timezoneName)` is not a constant and contains reference to `inv.timezoneName`. — Jansen, Jun 27 '21 at 06:57
@WernfriedDomscheit it doesn't work for me: `EXPLAIN PLAN SET statement_id='pysrXbZ2F4EpC13tu5hJ6Q==' for SELECT invoiceID FROM invoices inv WHERE SYS_EXTRACT_UTC(TIMESTAMP '1970-01-01 00:00:00 UTC' + NUMTODSINTERVAL(INVOICETIME, 'SECOND') ) BETWEEN SYS_EXTRACT_UTC(FROM_TZ(TIMESTAMP '2020-10-10 00:00:00', inv.timezoneName)) AND SYS_EXTRACT_UTC(FROM_TZ(TIMESTAMP '2020-10-10 23:59:59', inv.timezoneName))` shows `TABLE ACCESS FULL| INVOICES` — Jansen, Jun 28 '21 at 17:16

Wernfried Domscheit · Answer 1 · 2021-06-28T06:31:35.937

You cannot create an index on a specific time zone as already explained by @mathguy:

CREATE INDEX IND_INVOICETIME_LOCAL ON INVOICE (
    (TIMESTAMP '1970-01-01 00:00:00 UTC' + NUMTODSINTERVAL(INVOICETIME, 'SECOND')) AT TIME ZONE timezoneName
);

ORA-01743: only pure functions can be indexed

What you can do is to create an index on the UTC time:

CREATE INDEX IND_INVOICETIME_UTC ON INVOICE (
    TIMESTAMP '1970-01-01 00:00:00 UTC' + NUMTODSINTERVAL(INVOICETIME, 'SECOND')
);

Then the condition would be this:

WHERE TIMESTAMP '1970-01-01 00:00:00 UTC' + NUMTODSINTERVAL(INVOICETIME, 'SECOND') 
   BETWEEN FROM_TZ(TIMESTAMP '2020-10-10 00:00:00', timezoneName) 
       AND FROM_TZ(TIMESTAMP '2020-10-10 23:59:59', timezoneName)

Oracle compares TIMESTAMP WITH TIME ZONE values always at UTC time. Be aware, when you have a TIMESTAMP WITH TIME ZONE column and create an index on it, then Oracle adds a virtual column of SYS_EXTRACT_UTC(...) and the index is created on this virtual column. Using such an index/column properly can be challenging, see https://tonyhasler.wordpress.com/2010/09/04/tonys-tirade-against-timestamp-with-time-zone/

I think the proper use would be this:

WHERE SYS_EXTRACT_UTC(TIMESTAMP '1970-01-01 00:00:00 UTC' + NUMTODSINTERVAL(INVOICETIME, 'SECOND') ) 
    BETWEEN SYS_EXTRACT_UTC(FROM_TZ(TIMESTAMP '2020-10-10 00:00:00', inv.timezoneName)) 
        AND SYS_EXTRACT_UTC(FROM_TZ(TIMESTAMP '2020-10-10 23:59:59', inv.timezoneName))

Thanks! This is quite a good proposal, but it has a problem, that the index is not helping, because condition matches by `inv.timezoneName`. So it still seems, that with this data structure it is not possible to create helping index. — Jansen, Jun 26 '21 at 10:14

How to convert Unix timestamp to local date in Oracle?

1 Answers1