Database query in code

Question

I am using a sysdate query in code to obtain the last updated rows in the past 3 hours (should be configurable). The database runs in GMT timezone and the application runs in PST timezone. The query looks like this

select * from <table> where updated_Date < sysdate - numdtodsinterval(3,'hour')

What is the recommended/best practice to firing such queries from application which has different timezones from the db.

• Affe: That rather depends on how you generated updated_Date in the first place!
• Stephanie Page: What Affe said. PLUS the datatype of updated_date. Even though the name of the column has _date in it, doesn't mean DATE is it's datatype. --- just sayin'

Answer 1

I would have to say that the best practice for an application that covers a lot of timezones is to stop using the DATE datatype and start using the TIMESTAMP with TIMEZONE datatype.

I'm not saying that's easy, just sayin' that's probably a best practice for a couple of reasons.

The main is that it eliminates any and all ambiguity. If you use DATE, no one can tell what that value was calculated based on. You know. Maybe you have a comment on the column (ideal) but maybe overlooked. If it's a TIMESTAMP with TIMEZONE datatype, you've nailed a specific moment in time independent of my local clock.

But this assumes that you can change the database schema, which you probably can't. So this serves as a warning for your next date column.

• Stephanie Page: also depends on the version of oracle.

Answer 2

Fire it as is using jdbc.

Sysdate is interpreted on the server, so you should be fine. It'll return the updates of the last 3 hours, regardless of the client time zone. (If you saved the updated_date using sysdate as well that is!)