Oracle DateTime query with time zones

Question

I have a SQL Builder library that direcltly uses ADO.NET. I have a means of creating a select query with a greater-than-or-equal operator, like:

select *
from book
where book.date_created >= {some date}

My issue is that {some date} is going to always be in the UTC time zone, but it's being compared to the book.date_created column which is a TIMESTAMP(6) WITH TIME ZONE column, which will not be in the UTC timezone.

I can execute the query, but my results are off becuaes of timezone comparisons. My query is for all books where the date_created >= x, but some of the results returned are not greater than x because after subtracting 5 hours for the time zone, they are now less than x. The IDataRecord DateTime fields returned are converted to UTC using DateTime.SpecifyKind()

Can I form my query such that it interprets book.date_created in the UTC timezone?

Note: While I'd love to change my Oracle DB columns to not specify timezones, changing table structures is not something I can do.

Edit: Currently, {some date} is a SQL Parameter. It's backing datatype is a DateTime with UTC as the timezone. As a parameter, it is a TimestampWithTZ. The Value of the parameter is a DateTime with the kind specified as UTC as well.

Update: The issue seems to be related to my results set from the IDataRecord. When I pull DateTimes off, I use DateTime.SpecifyKind() to put them in UTC mode. The problem is, the date times come out as DateTimeKind.Unspecified. When converting from Unspecified to UTC, it just drops the timezone and declares it is UTC without changing the underlying value. I'm not sure how to have the IDataRecord pull in the TimeZone value.

is "some date" variable defined as a date, timestamp, or timestamp with time zone? Can u show some .net code used? — tbone
I assume you're using Oracle's enhanced ado.net (odp.net or ODAC), and using a OracleTimeStampTZ structure for "some date", correct? The problem may not be how you store the timestamp, but what you're comparing it with on the .NET side (which is why I asked for some .NET code). — tbone
I'm using plain ADO.NET. I think the problem may not be the comparison, but rather, the IDataRecord's DateTime kind value being Unspecified, for some reason. — Stealth Rabbi

Vincent Malgrat Vincent Malgrat · Accepted Answer · 2012-01-10T14:01:48

You need to use the FROM_TZ function that transforms a TIMESTAMP into a TIMESTAMP WITH TIME ZONE. For example, if you know that your variable is in UTC time (+0:00):

SELECT * 
  FROM book 
 WHERE date_created >= from_tz(<timestamp>, '+0:00');

Here's a sample script that shows the behaviour you describe (your local time zone should be set to +1:00):

CREATE TABLE t (tz TIMESTAMP(6) WITH TIME ZONE);
INSERT INTO t VALUES 
(to_timestamp_tz('20000101 00:00:00 +1:00','yyyymmdd hh24:mi:ss tzh:tzm'));
INSERT INTO t VALUES 
(to_timestamp_tz('20000101 00:00:00 -1:00','yyyymmdd hh24:mi:ss tzh:tzm'));

-- This will return two values instead of one
SELECT * 
  FROM t 
 WHERE tz >= to_timestamp('20000101 00:00:00', 'yyyymmdd hh24:mi:ss');

-- This query will return only one row
SELECT * 
  FROM t 
 WHERE tz >= from_tz (to_timestamp('20000101 00:00:00', 
                                   'yyyymmdd hh24:mi:ss'), '+0:00');

Oracle DateTime query with time zones

2 Answers