AppLib TRUNCATE TIMESTAMP

Declarations

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

Syntax

VALUES(APPLIB.TRUNCATE_TIMESTAMP(timestamp, periodStr));

Purpose

This function provides a way to truncate a timestamp to a specific date period.

Input

• STAMP: Time to truncate.
• PERIOD: Part of the timestamp to truncate; e.g. HOURLY, DAILY, etc.

Example

0: jdbc:luciddb:> values(applib.truncate_timestamp(timestamp'2011-6-15 16:52:13', 'HOURLY'));
+----------------------+
|        EXPR$0        |
+----------------------+
| 2011-06-15 16:00:00  |
+----------------------+
0: jdbc:luciddb:> values(applib.truncate_timestamp(timestamp'2011-6-15 16:52:13', 'DAILY'));
+----------------------+
|        EXPR$0        |
+----------------------+
| 2011-06-15 00:00:00  |
+----------------------+
0: jdbc:luciddb:> values(applib.truncate_timestamp(timestamp'2011-6-15 16:52:13', 'WEEKLY'));
+----------------------+
|        EXPR$0        |
+----------------------+
| 2011-06-13 00:00:00  |
+----------------------+
0: jdbc:luciddb:> values(applib.truncate_timestamp(timestamp'2011-6-15 16:52:13', 'MONTHLY'));
+----------------------+
|        EXPR$0        |
+----------------------+
| 2011-06-01 00:00:00  |
+----------------------+
0: jdbc:luciddb:> values(applib.truncate_timestamp(timestamp'2011-6-15 16:52:13', 'YEARLY'));
+----------------------+
|        EXPR$0        |
+----------------------+
| 2011-01-01 00:00:00  |
+----------------------+
0: jdbc:luciddb:> values(applib.truncate_timestamp(cast(null as timestamp), 'HOURLY'));
+---------+
| EXPR$0  |
+---------+
|         |
+---------+