Oracle Date field - Time issues

Question

We have two databases, in two separate locations. One of the databases resides in a separate time zone than our users.

The problem is that when the database that is located in a separate time zone is updated, with a Date value, the database automatically subtracts 1:00 hour from the Date it was passed.

The issue is that, when passing a NULL date (12:00:00), the DAY value is changed to a previous day.

The updates are done via stored procedures, and the front end is a VB.NET smartclient.

How would you handle this the proper way? I basically don't even want to store the TIME at all, but I can't seem to figure out how to do that.

Answer 1

Not clear on what datetime you want in the database, or what the application is passing.

Assume the user's PC is telling him it is Tuesday, 12:30am, and the clock on the Db server is saying Monday, 11:30pm.

If you insert a value for the 'current date' (eg TRUNC(SYSDATE)) then, as far as the database is concerned, it is still Monday. If you insert a value for the 'current time (eg SYSDATE), it is also still Monday. if you insert a value for the session's current time (eg CURRENT_TIMESTAMP) and timezone and ask the database to store it in the database, it will store 11:30pm. If you ask the database to store the datetime '2009-12-31 14:00:00', then that is what it will store. If you ask it to store the datetime/timezone '2009-12-31 14:00:00 +08:00', then you are in the advanced manual. You can ask the database to store timestamps with timezone data. Also consider daylight saving

Answer 2

I would investigate using the TRUNC function in your stored proc method that updates the table. If the data type in the method (that updates the table) is not a DATE type then use the to_date function in conjunction with the TRUNC function.

Answer 3

This is outside of the scope of the question you are asking, but I would recommend in ALL cases where users are accessing a database from different time zones, the server and database clocks time zone should be set to UTC. It is probably too late for that, but setting the datbase server to UTC eliminates the problems caused by daylight savings time and different time zones.

In my opionion, Date/Time data can and should always be stored in UTC. This data can be converted to local time at the point where it is presented to the user. Oracle actually makes this easy with the TIMESTAMP with TIME ZONE data type. It allows you to access the data either as UTC (SYS_EXTRACT_UTC) or local time (Local to the database server.)

It is never the same day all places in the world, so dates cannot be considered without time.

Of course another of my opinions is that Daylight Savings time should be eliminated. But that is another topic.