Add timezone to Oracle 'DATE' result

date, oracle

Solution

The `TO_TIMESTAMP(x)` call is doing two implicit conversions, so it only works if your `NLS_DATE_FORMAT` and `NLS_TIMESTAMP_FORMAT` are similar. The function takes a string argument, so you're really doing `TO_TIMESTAMP(TO_CHAR(x))`, and as you shouldn't rely on NLS settings you should really expand that to `TO_TIMESTAMP(TO_CHAR(x, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')`.

Clearly that just makes your slathering even worse, unless you do that in a function as TrippKinetics suggested. Or you could do it in a view or with a virtual column; but the client may not be keen on any of those either.

To do it entirely within the query you could use `CAST` instead of `TO_TIMESTAMP`, which is slightly better though more typing than your original:

SELECT TO_CHAR(FROM_TZ(CAST(x AS TIMESTAMP), 'America/New_York'),
               'YYYY-MM-DD HH24:MI:SSTZHTZM') AS x

If your session is always in the same time zone as the database, or you can reasonably set it so it is, then you can bypass the `FROM_TZ` part for each query:

ALTER SESSION SET TIME_ZONE = 'America/New_York';
SELECT TO_CHAR(CAST(x AS TIMESTAMP WITH TIME ZONE),
               'YYYY-MM-DD HH24:MI:SSTZHTZM') AS x

But you have to be sure you can/will always change the session.

And then if you are always changing the session anyway, this might be a legitimate time to set your `NLS_TIMESTAMP_TZ_FORMAT` (though I suspect I'll be downvoted for even thinking of suggesting it!), which would mean you could use an implicit conversion for display:

ALTER SESSION SET TIME_ZONE = 'America/New_York';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SSTZHTZM';
SELECT CAST(x AS TIMESTAMP WITH TIME ZONE) AS x

With your sample date/time and one in summertime, that gives:

X                       
-------------------------
2014-05-27 14:48:12-0400  
2013-12-14 15:12:46-0500

Once you've altered the session, you just need to wrap each date column you want in that expanded format in the `CAST()`. That's still a bit of a pain but more manageable. There's no way to do that completely transparently within the database, though using a view or virtual column comes close. Defining your own function or setting up the session so you can safely `CAST` are a bit more work.

Arguably relying on the session alter adds a bit of risk, uncertainty or confusion, but it's a trade-off for convenience.

And all of that assumes all the original dates really are all for a single time zone.

Problem

I'm using a client's Oracle 11G database, where they have a certain column `x` stored as a `DATE` type (without timezone). I happen to know through communication with them that this is considered local time for them, e.g. `America/New_York`. I would like to query the database to get values for this column in an unambiguous format like `2013-12-14 15:12:46-0500` that can be easily read correctly by other tools. I'm not seeing in the Oracle docs an easy way to imbue the column's value (at query time) with a timezone, then format it: ``` SELECT TO_CHAR(FROM_TZ(TO_TIMESTAMP(x), 'America/New_York'), 'YYYY-MM-DD HH24:MI:SSTZHTZM') AS x ``` But that's pretty ugly and I don't relish slathering that all over my queries whenever I need to retrieve a date. Is there a better solution? Convincing the client to change the column type isn't an option.

Original source