Time Literals - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

Language

English (United States)

Last Update

2024-04-03

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

Declares a time value in an expression.

Syntax

TIME 'string'

Syntax Elements

string

8- to 21-character string in one of these formats:

Format

Description

hh:mi:ss

Time with no fractional seconds digits or Time Zone:

Symbol	Meaning
hh	Hour of the day Valid range is 00–23, inclusive. You must specify both digits.
mi	Minute of the hour Valid range is 00–59, inclusive. You must specify both digits.
ss	Seconds Valid range is 00–61. You must specify both digits.

hh:mi:ss:signhh:mi

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

Symbol	Meaning
hh	Hour of the day Valid range is 00–23, inclusive. You must specify both digits.
mi	Minute of the hour Valid range is 00–59, inclusive. You must specify both digits.
ss	Seconds Valid range is 00–61. You must specify both digits.
signhh:mi	Hours and minutes in Time Zone offset Valid range is -12:59 through +13:00, inclusive.

hh:mi:ss.ssssss

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

Symbol	Meaning
hh	Hour of the day Valid range is 00–23, inclusive. You must specify both digits.
mi	Minute of the hour Valid range is 00–59, inclusive. You must specify both digits.
ss.ssssss	Seconds 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	Hour of the day Valid range is 00–23, inclusive. You must specify both digits.
mi	Minute of the hour Valid range is 00–59, inclusive. You must specify both digits.
ss.ssssss	Seconds 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	Hours and minutes in Time Zone offset Valid range is -12:59 through +13:00, inclusive.

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.

Format	Data Type
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.

Example 1: hh:mi:ss Format

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';

Example 2: hh:mi:sssignhh:mi Format

SELECT *
FROM Classes
WHERE start_time = TIME '10:37:12-08:00';

Example 3: hh:mi:ss.ssssss Format

SELECT *
FROM Classes
WHERE start_time = TIME '10:36:02.123456';

Example 4: hh:mi:ss.sssssssignhh:mi Format

SELECT Customer_ID
FROM Messages
WHERE start_time = TIME '21:17:35.123456+07:30'

Related Information

Topic	Reference
TIME data types	TIME Data Type.
TIME WITH TIME ZONE data types	TIME WITH TIME ZONE Data Type.