Clever Geek Handbook

Oracle Date Field - Time Matters

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

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

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

Updates are done using stored procedures and the front end is a VB.NET smart client.

How would you handle this properly? I don't want to store TIME at all, but I can't figure out how to do it.

+2


source to share


3 answers


It is not clear what time you want in the database, or what the application is sending.

Suppose the user's PC tells him it is Tuesday 12:30 pm and the clock on the Db server says Monday 11:30 pm.



If you enter a value for the current date (for example TRUNC (SYSDATE)), as far as the database is concerned, it is still Monday. If you insert a value for the current time (such as SYSDATE), it also stays on Monday. if you enter a value for the current session time (e.g. CURRENT_TIMESTAMP) and timezone and ask the database to store it in the database, it will store 11:30 pm. 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 him to save the date-time / time zone "2009-12-31 14:00:00 +08: 00" then you are in the advanced manual. You can query the database to store timestamps with timezone data . Also consider daylight saving time

+2


source


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



+1


source


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

In my openion, date and time data can and should always be stored in UTC. This data can be converted to local time at the place where it is presented to the user. Oracle actually makes it easier to work with TIMESTAMP with the TIME ZONE data type. It allows you to access data in UTC (SYS_EXTRACT_UTC) or local time (Local to the database server.)

This is not a day when all the places in the world are, so dates cannot be considered without time.

Of course, my other opinion is that daylight saving time should be eliminated. But this is another topic.

+1


source






More articles:


All Articles