15.10 - Time Literals - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Declares a time value in an expression.

where string specifies an 8- to 21-character string enclosed in apostrophes in one of four formats.

 

This format...

Represents...

hh:mi:ss

time with no fractional seconds digits or Time Zone:

  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  • ss represents seconds. The valid range is 00–61. You must specify both digits.
  • hh:mi:sssignhh:mi

    time with a specified Time Zone, but no fractional seconds digits:

  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  • ss represents seconds. The valid range is 00–61. You must specify both digits.
  • signhh:mi represents the hours and minutes in the Time Zone offset. The valid range is -12:59 through +13:00, inclusive.
  • sign is + or -

    hh:mi:ss.ssssss

    time with up to six fractional seconds digits, but no Time Zone:

  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  • ss.ssssss represents seconds. The valid range for the first two digits is 00–61. You must specify both digits. You can specify from one to six fractional digits.
  • hh:mi:ss.sssssssignhh:mi

    time with up to six fractional seconds and a Time Zone offset:

  • hh represents the hour of the day. The valid range is 00–23, inclusive. You must specify both digits.
  • mi represents minute of the hour. The valid range is 00–59, inclusive. You must specify both digits.
  • ss.ssssss represents seconds. The valid range for the first two digits is 00–61. You must specify both digits. You can specify from one to six fractional digits.
  • signhh:mi represents the hours and minutes in the Time Zone offset. The valid range is -12:59 through +13:00, inclusive.
  • sign is + or -

    The colons are required between the first three elements, and the decimal point is required if fractional seconds are specified. A decimal point is not allowed if there are no fractional digits. Spaces and new line characters are not allowed in a literal except after the keyword TIME.

     

    IF the format of the Time literal is …

    THEN the data type is …

    hh:mi:ss

    TIME(0).

    hh:mi:sssignhh:mi

    TIME(0) WITH TIME ZONE.

    hh:mi:ss.ssssss

    TIME(n), where n is the number of fractional seconds digits.

    hh:mi:ss.sssssssignhh:mi

    TIME(n) WITH TIME ZONE, where n is the number of fractional seconds digits.

    The following example selects all classes from the Classes table that start at 3:30 PM.

       SELECT *
       FROM Classes
       WHERE start_time = TIME '15:30:00';
       SELECT *
       FROM Classes
       WHERE start_time = TIME '10:37:12-08:00';
       SELECT *
       FROM Classes
       WHERE start_time = TIME '10:36:02.123456';
       SELECT Customer_ID
       FROM Messages
       WHERE start_time = TIME '21:17:35.123456+07:30'
     

    FOR information on …

    SEE …

    TIME data types

    “TIME Data Type” on page 139.

    TIME WITH TIME ZONE data types

    “TIME WITH TIME ZONE Data Type” on page 145.