All geek questions in one place

Time Zone Oracle DateTime Query

I have a SQL Builder library that directly uses ADO.NET. I have a way to create a select query with an operator of a greater or equal type, for example:

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

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

I can fulfill the query, but my results do not match the time zone comparison. My query is for all books where date_created> = x, but some returned results do not exceed x, since after subtracting 5 hours for the time zone, they are now less than x. Received IDataRecord DateTime fields are converted to UTC using DateTime.SpecifyKind ()

Can I format my request so that it interprets book.date_created in the UTC time zone?

Note. Although I would like to change the columns of Oracle DB to not specify time zones, changing the table structure is not what I can do.

Edit: Currently {some date} is an SQL parameter. It supports a DateTime data type with UTC as a time zone. As a parameter, this is TimestampWithTZ. The parameter value is a DateTime with the type specified as UTC.

Update: The problem seems to be related to my results set in IDataRecord. When I pull out DateTimes, I use DateTime.SpecifyKind () to put them in UTC. The problem is that the date is being issued as DateTimeKind.Unspecified. When converting from Unspecified to UTC, it simply cancels the time zone and announces that it is UTC without changing the base value. I'm not sure how to get IDataRecord to pull out the TimeZone value.

+4
source share
2 answers

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

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

Here's an example script that shows the behavior you are describing (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');
+3
source

Source: https://habr.com/ru/post/1390232/

More articles:


All Articles