15.10 - TIME and TIMESTAMP Formats - Teradata Database

Teradata Database SQL Data Types and Literals

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

Teradata SQL uses a default set of formats for the output of TIME and TIMESTAMP expressions and column data, and for comparison and conversion of TIME and TIMESTAMP data types.

You can change or override the TIME and TIMESTAMP format settings for field mode in the following ways:

  • Change the system default TIME and TIMESTAMP formats in the specification for data formatting (SDF) file.
  • Specify a format in a FORMAT phrase.
  • The FORMAT phrase sets the format for a specific TIME or TIMESTAMP column or value. A FORMAT phrase overrides the system format.

    The TIME and TIMESTAMP format setting pertains to data in report form, as is the case in BTEQ. The format does not control internal storage representation of data or data returned in record or indicator variable mode.

    For details about the FORMAT phrase, see “FORMAT Phrase and DateTime Formats” on page 318 or “FORMAT Phrase, DateTime Formats, and Japanese Character Sets” on page 326.

    The following table provides the system default formats.

     

    Data Type

    System Default Format

    TIME

    TIME WITH TIME ZONE

    HH:MI:SS.S(F)Z

    Time zone information is displayed for TIME WITH TIME ZONE, but not for TIME.

    TIMESTAMP

    TIMESTAMP WITH TIME ZONE

    YYYY-MM-DDBHH:MI:SS.S(F)Z

    Time zone information is displayed for TIMESTAMP WITH TIME ZONE, but not for TIMESTAMP.

    Your system administrator can change the default format for TIME and TIMESTAMP data types. Changing the default TIME and TIMESTAMP formats involves changing the values of the TIME and TIMESTAMP elements in the Specification for Data Formatting (SDF) file, and using the tdlocaledef utility to convert the information into an internal form usable by Teradata Database.

    Before changing the default format, consider the effect on the following.

     

    Data-entry format

    Changing the default formats changes the data entry format of TIME and TIMESTAMP data for new tables, but not for existing tables. To avoid data entry problems, however, you can enter all times and timestamps as ANSI TIME and TIMESTAMP literals.

    Display format

    Changing the default format changes the display of TIME and TIMESTAMP values in new tables, but not in existing tables, so values for new tables and old tables display differently. Because views are based on the underlying table, this is true for views of tables as well.

    Character-to-TIME and character-to-TIMESTAMP comparisons and conversions

    To compare or convert strings to TIME or TIMESTAMP values, the strings must have the same format as the TIME or TIMESTAMP values. Such comparisons can fail after you change the default format, because the format changes for new tables, views of new tables, and existing macros. To avoid format errors in character-to-TIME and character-to-TIMESTAMP comparisons, however, you can specify times and timestamps as ANSI TIME and TIMESTAMP literals.

    Format of TIME and TIMESTAMP values in macros being executed

    Changing the default format can change the format of TIME and TIMESTAMP values in macros, which can cause the macro to fail.

    Changing the default format does not change the TIME or TIMESTAMP format for the following:

  • Tables created prior to the format change
  • Tables created by users currently logged on
  • Macros executed by users currently logged on
  • Views based on tables that were created prior to the change
  • Use HELP SESSION to view the values of the system default TIME and TIMESTAMP formats.

    You can use the FORMAT phrase to set the format for a specific TIME or TIMESTAMP column or value. A FORMAT phrase overrides the TIME or TIMESTAMP system format.

    Setting a different TIME or TIMESTAMP format for a column with the FORMAT phrase has the same consequences as with any other method:

  • Data entry for that column must be in the new format or the ANSI TIME or TIMESTAMP literal format.
  • To compare or convert strings to TIME or TIMESTAMP values in the column, strings must match that format.
  • A FORMAT phrase does not change the export data type of TIME or TIMESTAMP values.

    For details about using the FORMAT phrase, see “FORMAT Phrase and DateTime Formats” on page 318 or “FORMAT Phrase, DateTime Formats, and Japanese Character Sets” on page 326.

    For details on the components that make up a TIME and TIMESTAMP format, see “Formatting Characters for Time Information” on page 320.

    The operations that set the TIME and TIMESTAMP formats are best described as a hierarchy, where one setting overrides another.

    At the lowest level, the system comes with a default TIME and TIMESTAMP format. Your system administrator can override the formats by creating a custom SDF that changes the default TIME and TIMESTAMP formats.

    You can override the system-level TIME or TIMESTAMP format for an individual column or individual value.

    The following table summarizes the hierarchy of TIME and TIMESTAMP formats.

     

    For this operation …

    The format is set for …

    And field mode display format …

    SELECT with FORMAT phrase

    an individual column

    as defined by FORMAT.

     

    CREATE / ALTER TABLE, with FORMAT phrase

    Default TIME or TIMESTAMP format in custom SDF

    the system

    as defined by the TIME and TIMESTAMP format in the SDF.

    The following table is a nonexhaustive list of formats that can be used to present an output time in field mode, where the data is 13:20:53.64+03:00.

     

    FORMAT Phrase

    Result

    FORMAT 'HH:MIBT'

    01:20 PM

    FORMAT 'HH:MI'

    13:20

    FORMAT 'HH.MI.SS'

    13.20.53

    FORMAT 'HH:MI:SSBT'

    01:20:53 nachm

    (nachm is German for PM.)

    FORMAT 'HH:MI:SSDS(F)'

    13:20:53.64

    FORMAT 'HH:MI:SSDS(F)Z'

    13:20:53.64+03:00

    FORMAT 'HHhMImSSs'

    13h20m53s

    The following table is a nonexhaustive list of formats for presenting an output timestamp in field mode, where the data is 85/09/12 13:20:53.64+03:00.

     

    FORMAT Phrase

    Result

    FORMAT 'MM/DD/YYBHH:MIBT'

    85/09/12 01:20 PM

    FORMAT 'MMMBDD,BYYBHH:MI:SS'

    Sep 12, 85 13:20:53

    FORMAT 'E3,BM4BDD,BY4BHH:MI:SSDS(F)'

    Thu, September 12, 1985 13:20:53.64

    FORMAT 'YYYY-MM-DDBHH:MI:SSDS(F)Z'

    1985-09-12 13:20:53.64+03:00