SQL Translator Support for Date, Time, and Timestamp Fields

In this section:

Reference:

Several new data types have been defined for the SQL Translator to support date-time fields in the WHERE predicate or field list of a SELECT statement.

In addition, time or timestamp columns can be defined in relational or FOCUS data sources, and are accessible to the translator. Values can be entered using INSERT and UPDATE statements, and displayed in SELECT statements.

Time or timestamp data items (columns or literals) can be compared in conditions. Time values or timestamp values can be added to or subtracted from each other, with the result being the difference in number of seconds. Expressions of the form T + 2 HOURS or TS + 5 YEARS are allowed. These expressions are translated to calls to the date-time functions described in the TIBCO WebFOCUS® Using Functions manual.

All date formats for actual and virtual fields in the Master File are converted to the form YYYYMMDD. If you specify a format that lacks any component, the SQL Translator supplies a default value for the missing component. To specify a portion of a date, such as the month, use a virtual field with an alphanumeric format.

Reference: SQL Translator Support for Date, Time, and Timestamp Fields

In the following chart, fff represents the second to three decimal places (milliseconds) and ffffff represents the second to six decimal places (microseconds).

The following formats are allowed as input to the Translator:

Format

USAGE Attribute in Master File

Date Components

Date

YYMD
YYYY-MM-DD

Hour

HH
HH

Hour through minute

HHI
HH.MM

Hour through second

HHIS
HH.MM.SS

Hour through millisecond

HHISs
HH.MM.SS.fff

Hour through microsecond

HHISsm
HH.MM.SS.ffffff

Year through hour

HYYMDH
YYYY-MM-DD HH

Year through minute

HYYMDI
YYYY-MM-DD HH.MM

Year through second

HYYMDS
YYYY-MM-DD HH.MM.SS

Year through millisecond

HYYMDs
YYYY-MM-DD HH.MM.SS.fff

Year through microsecond

HYYMDm
YYYY-MM-DD
HH.MM.SS.ffffff

Note:

  • Time information may be given to the hour, minute, second, or fraction of a second.
  • The separator within date information may be either a hyphen or a slash.
  • The separator within time information must be a colon.
  • The separator between date and time information must be a space.

Extracting Date-Time Components Using the SQL Translator

How to:

The SQL Translator supports several functions that return components from date-time values. Use the EXTRACT statement to extract components.

Use the TRIM function to remove leading and/or trailing patterns from date, time, and timestamp values. See the TIBCO WebFOCUS® Using Functions manual.

Syntax: How to Use Date, Time, and Timestamp Functions Accepted by the SQL Translator

The following functions return date-time components as integer values. Assume x is a date-time value:

Function

Return Value

YEAR(x)

year

MONTH(x)

month number

DAY(x)

day number

HOUR(x)

hour

MINUTE(x)

minute

SECOND(x)

second

MILLISECOND(x)

millisecond

MICROSECOND(x)

microsecond

Example: Using SQL Translator Date, Time, and Timestamp Functions

Using the timestamp column TS whose value is '1999-11-23 07:32:16.123456':

YEAR(TS) = 1999
MONTH(TS) = 11
DAY(TS) = 23
HOUR(TS) = 7
MINUTE(TS) = 32
SECOND(TS) = 16
MILLISECOND(TS) = 123
MICROSECOND(TS) = 123456

Example: Using SQL Translator Date, Time, and Timestamp Functions in a SELECT Statement

Assume that a FOCUS data source called VIDEOTR2 includes a date-time field named TRANSDATE.

SQL
SELECT TRANSDATE,
YEAR(TRANSDATE), MONTH(TRANSDATE),
MINUTE(TRANSDATE)
FROM VIDEOTR2;
FILE VIDSQL 
END

The SQL Translator produces the following virtual fields for functions, followed by a TABLE request to display the output:

SET COUNTWIDTH=ON
-SET SQLERRNUM = 0;
DEFINE FILE
VIDEOTR2 TEMP
SQLDEF01/I4 MISSING ON NEEDS ALL = HPART(TRANSDATE,'YEAR','I4');
SQLDEF02/I2 MISSING ON NEEDS ALL = HPART(TRANSDATE,'MONTH','I2');
SQLDEF03/I2 MISSING ON NEEDS ALL = HPART(TRANSDATE,'MINUTE','I2');
END
TABLEF FILE VIDEOTR2
PRINT TRANSDATE SQLDEF01 AS 'SQLDEF01' SQLDEF02 AS 'SQLDEF02' SQLDEF03 AS
  'SQLDEF03'
ON TABLE SET CARTESIAN ON
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END

The output is:

TRANSDATE         SQLDEF02  SQLDEF04  SQLDEF05
1999/06/20 04:14      1999         6        14
1991/06/27 02:45      1991         6        45
1996/06/21 01:16      1996         6        16
1991/06/21 07:11      1991         6        11
1991/06/20 05:15      1991         6        15
1999/06/26 12:34      1999         6        34
1919/06/26 05:45      1919         6        45
1991/06/21 01:10      1991         6        10
1991/06/19 07:18      1991         6        18
1991/06/19 04:11      1991         6        11
1998/10/03 02:41      1998        10        41
1991/06/25 01:19      1991         6        19
1986/02/05 03:30      1986         2        30
1991/06/24 04:43      1991         6        43
1991/06/24 02:08      1991         6         8
1999/10/06 02:51      1999        10        51
1991/06/25 01:17      1991         6        17

Syntax: How to Use the SQL Translator EXTRACT Function to Extract Date-Time Components

Use the following ANSI standard function to extract date-time components as integer values:

EXTRACT(component FROM value)

where:

component

Is one of the following: YEAR, MONTH, QUARTER, DAY, WEEKDAY, HOUR, MINUTE, SECOND, MILLISECOND, or MICROSECOND.

value

Is a date-time, DATE, TIME, or TIMESTAMP field, constant or expression.

For example, the following are equivalent:

EXTRACT(YEAR FROM TS)
YEAR(TS)

Example: Using the EXTRACT Function

SELECT D. EXTRACT(YEAR FROM D), EXTRACT(MONTH FROM D),
EXTRACT(DAY FROM D) FROM T

This request produces rows similar to the following:

1999-01-01     1999      1       1
2000-03-03     2000      3       3