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.

That rather depends on how you generated updated_Date in the first place! — Affe
– Affe, Commented Jan 17, 2011 at 23:35
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' — Stephanie Page
– Stephanie Page, Commented Jan 17, 2011 at 23:46

Stephanie Page · Accepted Answer · 2011-01-17 23:54:47Z

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.

answered Jan 17, 2011 at 23:54

community wiki

Stephanie Page

Sign up to request clarification or add additional context in comments.

1 Comment

Stephanie Page Over a year ago

also depends on the version of oracle.

Axel Fontaine · Accepted Answer · 2011-01-17 23:31:45Z

1

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!)

answered Jan 17, 2011 at 23:31

Axel Fontaine

35.4k19 gold badges110 silver badges144 bronze badges

Collectives™ on Stack Overflow

Database query in code

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related