Teradata 简明教程

Teradata - Date/Time Functions

本章讨论 Teradata 中提供的日期/时间函数。

This chapter discusses the date/time functions available in Teradata.

Date Storage

日期使用以下公式在内部存储为整数。

Dates are stored as integer internally using the following formula.

((YEAR - 1900) * 10000) + (MONTH * 100) + DAY

您可以使用以下查询检查日期的存储方式。

You can use the following query to check how the dates are stored.

SELECT CAST(CURRENT_DATE AS INTEGER);

由于日期存储为整数，因此您可以在日期上执行一些算术运算。Teradata 提供可执行这些运算的函数。

Since the dates are stored as integer, you can perform some arithmetic operations on them. Teradata provides functions to perform these operations.

EXTRACT

EXTRACT 函数从 DATE 值中摘取日期、月份和年份的部分。该函数也用于从 TIME/TIMESTAMP 值中摘取小时、分钟和秒。

EXTRACT function extracts portions of day, month and year from a DATE value. This function is also used to extract hour, minute and second from TIME/TIMESTAMP value.

Example

以下示例显示如何从日期和时间戳值中摘取年份、月份、日期、小时、分钟和秒值。

Following examples show how to extract Year, Month, Date, Hour, Minute and second values from Date and Timestamp values.

SELECT EXTRACT(YEAR FROM CURRENT_DATE);
EXTRACT(YEAR FROM Date)
-----------------------
        2016
SELECT EXTRACT(MONTH FROM CURRENT_DATE);
EXTRACT(MONTH FROM Date)
------------------------
          1
SELECT EXTRACT(DAY FROM CURRENT_DATE);
EXTRACT(DAY FROM Date)
------------------------
          1

SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);
EXTRACT(HOUR FROM Current TimeStamp(6))
---------------------------------------
                 4
SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP);
EXTRACT(MINUTE FROM Current TimeStamp(6))
-----------------------------------------
                 54
SELECT EXTRACT(SECOND FROM CURRENT_TIMESTAMP);
EXTRACT(SECOND FROM Current TimeStamp(6))
-----------------------------------------
              27.140000

INTERVAL

Teradata 提供 INTERVAL 函数对 DATE 和 TIME 值执行算术运算。有两种类型的 INTERVAL 函数。

Teradata provides INTERVAL function to perform arithmetic operations on DATE and TIME values. There are two types of INTERVAL functions.

Year-Month Interval

  1. YEAR

  2. YEAR TO MONTH

  3. MONTH

Day-Time Interval

  1. DAY

  2. DAY TO HOUR

  3. DAY TO MINUTE

  4. DAY TO SECOND

  5. HOUR

  6. HOUR TO MINUTE

  7. HOUR TO SECOND

  8. MINUTE

  9. MINUTE TO SECOND

  10. SECOND

Example

以下示例将 3 年添加到当前日期。

The following example adds 3 years to current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03' YEAR;
  Date    (Date+ 3)
--------  ---------
16/01/01   19/01/01

以下示例将 3 年和 01 个月添加到当前日期。

The following example adds 3 years and 01 month to current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03-01' YEAR TO MONTH;
 Date     (Date+ 3-01)
--------  ------------
16/01/01    19/02/01

以下示例将 01 天、05 小时和 10 分钟添加到当前时间戳。

The following example adds 01 day, 05 hours and 10 minutes to current timestamp.

SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP + INTERVAL '01 05:10' DAY TO MINUTE;
     Current TimeStamp(6)         (Current TimeStamp(6)+ 1 05:10)
--------------------------------  --------------------------------
2016-01-01 04:57:26.360000+00:00  2016-01-02 10:07:26.360000+00:00