AppLib EXTRACT TIMESTAMP

Declarations

create or replace function applib.extract_timestamp(
  stamp timestamp,
  field varchar(128))
returns int
language java
deterministic
no sql
returns null on null input
external name 'applib.applibJar:org.eigenbase.applib.contrib.TimestampUtilUdf.extractTimestamp';

Syntax

VALUES(APPLIB.EXTRACT_TIMESTAMP(timestamp, fieldStr));

Purpose

This function provides a way to read out the number for some portion of a timestamp.

Input

• STAMP: Source time.
• FIELD: Field to read out, e.g. 'SECOND', 'DOW' (Day Of Week), 'HOW' (Hour Of Week).

Example

0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'SECOND'));
+---------+
| EXPR$0  |
+---------+
| 13      |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'MINUTE'));
+---------+
| EXPR$0  |
+---------+
| 52      |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'HOUR'));
+---------+
| EXPR$0  |
+---------+
| 16      |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'DAY'));
+---------+
| EXPR$0  |
+---------+
| 15      |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'DOW'));
+---------+
| EXPR$0  |
+---------+
| 4       |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-13 16:52:13', 'DOW'));
+---------+
| EXPR$0  |
+---------+
| 2       |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-12 16:52:13', 'DOW'));
+---------+
| EXPR$0  |
+---------+
| 1       |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'WEEK'));
+---------+
| EXPR$0  |
+---------+
| 25      |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'MONTH'));
+---------+
| EXPR$0  |
+---------+
| 5       |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'YEAR'));
+---------+
| EXPR$0  |
+---------+
| 2011    |
+---------+
0: jdbc:luciddb:> values(applib.extract_timestamp(timestamp'2011-6-15 16:52:13', 'HOW'));
+---------+
| EXPR$0  |
+---------+
| 88      |
+---------+