Database Tips: Time Format

Date Time fields in our databases include both a date and a time component. While we are all pretty clear about the when things happen at 2am or 3pm, and can even calculate times in 24 hour for those military types but be careful at midday and midnight.

The Time component can be displayed in several formats, so we recommend you select a format and hard code it in your report. Regional settings can vary from machine to machine and this can change the way they display to different users.

Midnight is a time value of 00:00:00 and midday is 12:00:00. This will appear as 12am and 12pm respectively. The place it causes confusion is when you add ten minutes to 11:58am to get 12:08pm, which is followed by 1:08pm an hour later.

To avoid confusion, always use 24 hour time, and keep away from that am/pm format. It is too easy to slip into the wrong 12 hour period.

This applies to both field display and parameter input in your reports. There is an exception, however. 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.

All this talk of time and dates brings us to a related issue: UTC.

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.

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.