Introduction
Dealing with dates
Working with time data
12. Using logical operators with time
Date and time date
Extracting dates and times
Doing arithmetic with dates
Converting date and time data
Current date and time data
Building date and time data from parts
Summary and review

Instruction

As you can see, the format for time data is

hh:mm:ss[.n*]

Breaking this down, we see that:

  • hh stands for hours. Each hour is represented by two digits from 00 to 23.
  • mm stands for minutes. Each minute is represented two digits from 00 to 59.
  • ss stands for seconds. Each second is represented by two digits from 00 to 59.
  • n* stands for fractional seconds and is optional. It ranges from 0 to 9999999. By default, this is set to seven digits.

So there we have the TIME data type. Its default value is 00:00:00. You'll also notice that TIME uses a 24-hour clock, so you'd write, for example, 14:25 for 2:25 PM.

You can set the precision for the fractional seconds by enclosing a value in parentheses, while declaring a column:

  • TIME(1)—formats all time data as hh:mm:ss.n,
  • TIME(5)—formats all time data as hh:mm:ss.nnnnn.

Note that TIME and TIME(7) are the same. Also, it's good to know that TIME(0) takes only 3 bytes, but TIME(7) takes 5 bytes. We recommend setting the precision of TIME to just what you need for the values in that column.

Setting a value in a TIME column requires quotes:

SELECT
  Code,
  DepartureTime
FROM Route
WHERE ArrivalTime = '20:15:00';

You can also use other comparison operators with TIME values, including >, >=, <, <=, and !=.

Exercise

Show the Code number of the route arriving at 9:30 AM.

Stuck? Here's a hint!

Remember the proper time format; don't use AM. Use quotes around the time value: 'hh:mm:ss'.