Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone.

How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

To be precise, the date type stores timestamps without any timezone information rather than "in local time zone".

How do I create an index on the column that can be used when queried with UTC timestamps and how to format the query?

You have three options:

  • index the date and convert yourtimestamptodate` when querying, eg:

    select * from foo where foo_date=cast(systimestamp as date) from dual;
    
  • create a functional index on the date converted to a timestamp with local time zone, eg:

    create unique index i_foo on foo(cast(ts as timestamp with local time zone));
    select * from foo where cast(ts as timestamp with local time zone)=
                            cast(X as timestamp with local time zone);
    
  • change the type of your columns of type date to timestamp with time zone or timestamp with local time zone

Of these, it is very likely that the last option is the best one - timestamp with local time zone is the 'correct' type for most data that refers to a fixed point in time. (As opposed to 'noon' which is always 12:00 no matter which time zone you are in.)

share|improve this answer
    
I agree that the best option is the last one - but changing the database schema is not an option in this case unfortunately. As for the functional index, I'd love to confirm how that is done so that it really works properly. This tonyhasler.wordpress.com/2010/09/04/… page lists some problems in doing such an index and I'm not sure how recent of an Oracle this is... –  Nakedible Nov 30 '11 at 10:15
    
I agree with the blog that there are some quirks with timestamp with time zone :) I've added an example for the functional index case, but it seems to me no more convenient that converting to date when querying - either way you'll have to change your queries. –  Jack Douglas Nov 30 '11 at 15:50
    
Thank you very much! As a quick fix we are probably going with the CAST(? AS DATE) option and I managed to confirm that it works already. The underlying issue now is that the timezone the timestamps are in might have DST, so some hours might be filled twice in the database :( –  Nakedible Dec 1 '11 at 7:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.