Add timezone to Oracle 'DATE' result

Question

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.

Answer 1

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.