Timestamp Literals - Advanced SQL Engine

Timestamp Literals - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product

Advanced SQL Engine

Teradata Database

Release Number

17.05

17.00

Published

June 2020

Language

English (United States)

Last Update

2021-01-22

dita:mapPath

zsn1556242031050.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1143

lifecycle

Product Category

Teradata Vantage™

Declares a timestamp value in an expression.

Syntax

TIMESTAMP 'string'

where string specifies a character string in apostrophes in one of four formats.

YYYY-MM-DD hh:mi:ss

Timestamp with no time zone or fractional seconds digits:

YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits.
DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
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.

YYYY-MM-DD hh:mi:sssignhh:mi

A timestamp with a specified time zone offset, but no fractional seconds digits.

YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits
DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
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 -

YYYY-MM-DD hh:mi:ss.ssssss

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

YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits.
DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
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.

YYYY-MM-DD

hh:mi:ss.sssssssignhh:mi

A timestamp with up to six fractional seconds and a time zone offset:

YYYY represents year. The valid range is 0001 through 9999, inclusive. You must specify all four digits.
MM represents month. The valid range is 01 through 12, inclusive. You must specify both digits.
DD represents day. The valid range is 01 through 31, inclusive, constrained by Gregorian calendar definitions. You must specify both digits, and follow them by a single pad character.
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 -

Usage Notes

The colons are required between the hour, minute, and second 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 TIMESTAMP.

Data Types

IF the format of the Timestamp literal is …	THEN the data type is …
YYYY-MM-DD hh:mi:ss	TIMESTAMP(0).
YYYY-MM-DD hh:mi:sssignhh:mi	TIMESTAMP(0) WITH TIME ZONE.
YYYY-MM-DD hh:mi:ss.ssssss	TIMESTAMP(n), where n is the number of fractional seconds digits.
YYYY-MM-DD hh:mi:ss.sssssssignhh:mi	TIMESTAMP(n) WITH TIME ZONE, where n is the number of fractional seconds digits.

Example 1: YYYY-MM-DD hh:mi:ss Format

The following example selects all classes from the Classes table that are timestamped November 23 2006 at 3:30:23 PM.

SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '2006-11-23 15:30:23';

Example 2: YYYY-MM-DD hh:mi:sssignhh:mi Format

SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '2002-01-01 10:37:12-08:00'

Example 3: YYYY-MM-DD hh:mi:ss.ssssss Format

SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '1995-07-31 10:36:02.123456'

Example 4: YYYY-MM-DD hh:mi:ss.sssssssignhh:mi Format

SELECT *
FROM Classes
WHERE Time_stamp = TIMESTAMP '1492-10-27 21:17:35.456123+07:30'

FOR information on …	SEE …
TIMESTAMP data types	TIMESTAMP Data Type.
TIMESTAMP WITH TIME ZONE data types	TIMESTAMP WITH TIME ZONE Data Type.