Product Documentation

V11.5 Update Guide

Previous Topic

Next Topic

Conditional expression functions to convert Unix time_t fields to c-tree Date and Time types

Conditional expression functions have been added to convert Unix time_t fields to c-tree Date and Time types. Customers using native Unix time_t types for data and time storage will need these functions to convert the Unix types to c-tree data types for use in partitioned file and conditional index expressions. Three new conditional expression functions have been added for this conversion:

  • TIMET2CTDATE - Convert integer time_t value to a c-tree Date type (CTDATE).
  • TIMET2CTTIME - Convert integer time_t value to a c-tree Time type (CTTIME).
  • TIMET2CTTIMES - Convert integer time_t value to a c-tree Timestamp type (CTTIMES).

Note: These functions use ctrt_gmtime_r as the time conversion routine. This corresponds to the initial Unix date partitioned file support in ctpart.c.

Example

Usage in a partitioned file expression to partition into months since Jan, 2010:

CREATE TABLE unixtest (name CHAR(10), u_date INTEGER)

or

CREATE TABLE unixtest (name CHAR(10), u_date BIGINT)

CREATE INDEX unixtest_date_idx ON unixtest (u_date) STORAGE_ATTRIBUTES 'partition=( ( YEAR( TIMET2CTDATE( u_date) ) -2010) * 12) + MONTH ( TIMET2CTDATE(u_date))'

Date String Unix Date Partition created

Mon, 23 Feb 2015 11:01:32 GMT 1424689292 62

Sat, 23 Jan 2016 11:01:32 GMT 1453546892 73

Tue, 23 Feb 2016 11:01:32 GMT 1456225292 74

Wed, 23 Mar 2016 11:01:32 GMT 1458730892 75

TOCIndex