Interval Data Types - Advanced SQL Engine - Teradata Database

Database Design
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kko1591750222108.ditamap
dita:ditavalPath
kko1591750222108.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™
INTERVAL YEAR: 2 Bytes (All Platforms)
Years Precision
1 2 3 4
Range
-9  —  9 Not used
-99  —  99 (default) Not used
-999  —  999 Not used
-9999  —  9999
INTERVAL YEAR TO MONTH: 4 Bytes (All Platforms)
Years Precision Months Precision
1 2 3 4
Range Range
-9  —  9 Not used
-99  —  99 Not used
-999  —  999 Not used
-9999  —  9999 00  —  11
INTERVAL MONTH: 2 Bytes (All Platforms)
                                                                      Months Precision
                  1                   2                   3                   4
Range
-9  —  9 Not used
-99  —  99 Not used
-999  —  999 Not used
-9999  —  9999
INTERVAL DAY: 2 Bytes (All Platforms)
                                                                          Days Precision
                  1                   2                   3                   4
                                                                                 Range
-9  —  9 Not used
-99  —  99 Not used
-999  —  999 Not used
-9999  —  9999
INTERVAL DAY TO HOUR: 4 Bytes (All Platforms)
                                           Days Precision Hours Precision
          1           2           3           4
                                                   Range  
-9  —  9 Not used
-99  —  99 (default) Not used
-999  —  999 Not used
-9999  —  9999 00  —  23
INTERVAL DAY TO MINUTE: 8 Bytes, including 2 pad bytes (All Platforms)
                                            Days Precision Minutes Precision
          1           2           3           4
Range  
-9  —  9 Not used 00  —  59
-99  —  99 (default) Not used
-999  —  999 Not used
-9999  —  9999
INTERVAL DAY TO SECOND:  10 Bytes (Packed64 Platforms) 12 Bytes (Aligned Row Platforms)  
                          Days Precision                                                 Seconds Precision
      1        2        3        4           1        2        3        4        5        6
Range Range
-9  —  9 Not used 00.0  —  59.9 Not used
-99  —  99 (default) Not used 00.00  —  59.99 Not used
-999  —  999 Not used 00.000  —  59.999 Not used
-9999  —  9999 00.0000  —  59.9999 Not used
00.00000  —  59.99999 Not used
00.000000  —  59.999999 (default)
INTERVAL HOUR: 2 Bytes (All Platforms)
                                                                     Hours Precision
                  1                   2                   3                   4
                                                                               Range
-9 — 9 Not used
-99 — 99 (default) Not used
-999 — 999 Not used
-9999 — 9999
INTERVAL HOUR TO MINUTE: 4 Bytes (All Platforms)
                                           Hours Precision Minutes Precision
           1            2            3            4
                                                   Range
-9  —  9 Not used 00  —  59
-99  —  99 (default) Not used
-999  —  999 Not used
-9999  —  9999
INTERVAL HOUR TO SECOND: 8 Bytes (All Platforms)
                       Hours Precision  Seconds Precision
      1       2       3       4           1        2        3        4        5        6
                                 Range                                                                 Range
-9  —  9 Not used 00.0  —  59.9 Not used
-99  —  99 (default) Not used 00.00  —  59.99 Not used
-999  —  999 Not used 00.000  —  59.999 Not used
-9999  —  9999 00.0000  —  59.9999 Not used
00.00000  —  59.99999 Not used
00.000000  —  59.999999
INTERVAL MINUTE: 2 Bytes (All Platforms)
                                                                      Minutes Precision
                  1                   2                   3                   4
                                                                                  Range
-9  —  9 Not used
-99  —  99 Not used
-999  —  999 Not used
-9999  —  9999
INTERVAL MINUTE TO SECOND: 6 Bytes (Packed64 Platforms)

8 Bytes (Aligned Row Platforms)

  
                       Minutes Precision Seconds Precision
     1      2      3      4          1        2       3        4        5       6
                                Range Range
-9  —  9 Not used 0.0  —  59.9 Not used
-99  —  99 (default) Not used 0.00  —  59.99 Not used
-999  —  999 Not used 0.000  —  59.999 Not used
-9999  —  9999 0.0000  —  59.9999 Not used
0.00000  —  59.99999 Not used
0.000000  —  59.999999
INTERVAL SECOND: 6 Bytes (Packed64 Platforms)

8 Bytes (Aligned Row Platforms)

  
                     Seconds Precision                                    Fractional Seconds Precision
      1       2       3       4          1       2       3       4       5       6
                                Range                                                             Range
-9  —  9 Not used 0.0  —  0.9 Not used
-99  —  99 (default) Not used 0.00  —  0.99 Not used
-999  —  999 Not used 0.000  —  0.999 Not used
-9999  —  9999 0.0000  —  0.9999 Not used
0.00000  —  0.99999 Not used
0.000000  —  0.999999 (default)

This table shows the alignments for interval types and their respective sizes on both platforms. When the size of the stored value for a type differs for the platforms, the cells are shaded.

Data Type Packed 64 Size (bytes) 64-Bit Aligned Row Size (bytes) Allocated Aligned Row Size (bytes)
INTERVAL YEAR 2 2 2
INTERVAL YEAR TO MONTH 4 2 4
INTERVAL MONTH 2 2 2
INTERVAL DAY 2 2 2
INTERVAL DAY TO HOUR 4 2 4
INTERVAL DAY TO MINUTE 8 2 8
INTERVAL DAY TO SECOND 10 4 12
INTERVAL HOUR 2 2 2
INTERVAL HOUR TO MINUTE 4 2 4
INTERVAL HOUR TO SECOND 8 4 8
INTERVAL MINUTE 2 2 2
INTERVAL MINUTE TO SECOND 6 4 8
INTERVAL SECOND 6 4 8