Time in SQL query top ready to use examples for your project

Most databases live and work in one time zone. If you have a database that covers more than one time zone, you might consider storing time in UTC and adding a numeric column to hold the local time-zone offset.The time zones start at UTC, which has an offset of zero. This is how the system-level time-zone table in Standard SQL is defined.

Theory Behind Time

There are also ISO-standard three-letter codes for the time zones of the world, such as EST, for Eastern Standard Time, in the United States.

The offset is usually a positive or negative number of hours, but there were some odd zones that differed by 15 minutes from the expected pattern; these were removed in 1998.

Now you have to factor in daylight saving time on top of that to get what is called “lawful time,” which is the basis for legal agreements.

The U.S. government uses DST on federal lands inside states that do not use DST. If the hardware clock in the computer in which the database resides is the source of the timestamps, you can get a mix of gaps and duplicate times over a year. This is why Standard SQL uses UTC internally. You should use a 24-hour time format. 24-hour time is less prone to errors than 12-hour (A.M./P.M.) time, since it is less likely to be misread or miswritten.

This format can be manually sorted more easily, and is less prone to computational errors. Americans use a colon as a field separator between hours, minutes and seconds; Europeans use a period.

(This is not a problem for them, since they also use a comma for a decimal point.) Most databases give you these display options.

3 Kinds of Times

• Fixed Event
• Duration
• Intervals

Fixed Event

One of the major problems with time is that there are three kinds— fixed events (“He arrives at 13:00”), durations (“The trip takes three hours”), and intervals (“The train leaves at 10:00 and arrives at 13:00”)—which are all interrelated.

• Standard SQL introduces an INTERVAL data type that does not explicitly exist in most current implementations (Rdb, from Oracle Corporation, is an exception). 
• An INTERVAL is a unit of duration of time, rather than a fixed point in time—days, hours, minutes, seconds. There are two classes of intervals. One class, called year-month intervals, has an express or implied precision that includes no fields other than YEAR and MONTH, though it is not necessary to use both. 
• The other class, called day-time intervals, has an express or implied interval precision that can include any fields other than YEAR or MONTH—that is, DAY, HOUR, MINUTE, and SECOND (with decimal places).