Isolating the hour, minutes, seconds, or fractional seconds of a time

The HOUR function returns the hour part of a value. The argument must be a time, timestamp, or a decimal number that is interpreted as a time.

For example, if you want to query the data in the Q.INTERVIEW table to determine the time that a particular interview ended, you could run a query like the following:

SELECT TEMPID, ENDTIME
  FROM Q.INTERVIEW
  WHERE TEMPID = 400

QMF shows the ENDTIME column and the time displays the hours, minutes, and seconds, as shown in the following figure.

Figure 1. A report that shows a specific time an interview ended

   TEMPID  ENDTIME
   ------  --------
      400  15.12.00

To show only the hour that the interview ended, you could run a query like the following:

SELECT TEMPID, HOUR(ENDTIME)
  FROM Q.INTERVIEW
  WHERE TEMPID = 400

QMF produces the report shown in the following figure, which shows only the hour portion of ENDTIME:

Figure 2. A report showing the hour portion of a timestamp

                
   TEMPID         COL1
   ------  -----------
      400           15

The MINUTE function returns the minute part of a value. The argument must be a time, timestamp, or a decimal number that is interpreted as a duration of hours, minutes, or seconds.

For example, consider the following SQL statement:

SELECT TEMPID, MINUTE(ENDTIME)
  FROM Q.INTERVIEW
  WHERE TEMPID = 400

When you run this query, QMF produces the report shown in the following figure:

Figure 3. A report showing the minute portion of a timestamp

                 
   TEMPID         COL1
   ------  -----------
      400           12

The SECOND function returns the seconds part of a value. The argument must be a time, timestamp, or a decimal number that is interpreted as a duration of hours, minutes, or seconds. For example, consider the following SQL statement:

SELECT TEMPID, SECOND(ENDTIME)
  FROM Q.INTERVIEW
  WHERE TEMPID = 400

When you run this query, QMF produces the report shown in the following figure:

Figure 4. A report showing the seconds portion of a timestamp

   TEMPID         COL1
   ------  -----------
      400            0

The MICROSECOND function returns the part of a value that represents fractional seconds. The argument can only be a column that contains timestamp data. For example, consider the following SQL statement, where the argument for the MICROSECOND function is a column named TIMESTAMP in the Q.PROJECT sample table:

SELECT PROJNO, MICROSECOND(TIMESTAMP)
  FROM Q.PROJECT
  WHERE PROJNO = '1409'

When you run this query, QMF produces the report shown in the following figure:

Figure 5. A report showing the portion of a timestamp that represents fractional seconds

   PROJNO         COL1
   ------  -----------
   1409         149572

The portion of a timestamp that represents fractional seconds can be from zero to 12 characters. The TIMESTAMP column of the Q.PROJECT sample table has a timestamp precision of six characters.