Database Tips: Use UTC Time Fields for Your Time-based Data

If you are working with date time fields and care about business hours between 7am and 7pm (or slightly later) only, then regular date time fields with local time will be fine.

But if your clock runs 24 hours and/or 7 days a week, and you cover multiple time zones, or have daylight wasting time, then you need to be careful. Real-world examples of such environments include air travel, factory processing, and energy billing.

You will need two date time fields in your database. One with the local time, and one with the time in UTC (Universal Time Coordinates).

UTC is what we used to call Greenwich Mean Time (GMT). There are two hours a year when you cannot do accurate time variance calculations using local time only. These are the hours when daylight wasting moves forward and back--they will make these calculations wrong. You need an equivalent period without the daylight wasting component. UTC is the solution to this.

But should you express time in the 12 hour format or the 24 hour format? Here are some considerations:

• The 12 hour format adds a layer of difficulty to time difference calculations.
• It's easy to drop off the AM or PM, and sometimes these do not display correctly. On older displays with discrete 8-segment LEDs, one failed segment would render AM as PM regardless of the actual time. With today's displays, that's less likely but still a consideration.
• The AM and PM are more information to carry in the system and more information for the user to process.
• Quick: What time is 12 PM? Did you answer midnight or mid-day? See the problem?
• Many people are not used to the 24 hour clock. They read 1300 and think that means 3PM. However, many people are ALSO confused by the 12 hour clock.
• Many people are uncomfortable with a 24 hour clock. This is probably the only reason to ever use a 12 hour clock. It's a concession to custom. That's why we still see 12 hour times given on Websites that operate 24 hours a day and in all time zones.

So, keep in mind that the better clock format may be the wrong choice. Find out if there's a strong dislike for the 24 hour time format before implementing it.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.