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