Overview: Date and Time Data Types

Summary

This article explains the differences found across the date, date time, and time data types supported by Connect and example use cases for each type.

Details

Xactly Connect supports the following date and time related data types. The data types are derived from Java 8.

Data Type	Description
date	`Date` values follow the ISO 8601 date format, yyyy-MM-dd. Example: “2016-11-29” `Date` is useful for logging events that occur daily and do not require timestamp or timezone information. The ToDate function converts date strings to `date`, whereas FormatDateTime can convert `date` values to strings using different masking pattern. CurDate returns the current Pacific timezone system date. UTCDate returns the current UTC date. Values returned by both functions lack timezone information.
date_time	`Date_time` builds on `date` by adding timestamp data for hours, minutes, seconds, and milliseconds (HH:mm:ss.SS). The time portion is a simple timestamp that lacks any offset or other timezone information. Example: “2016-11-29T11:44:05.428” CurDateTime returns the current Pacific timezone system `date_time`. UTCDateTime returns the current system `date_time` aligned to UTC. Values returned by both functions lack timezone information.
instant	`Instant` further builds upon `date_time` by adding UTC time zone information. This data type is useful for logging events that occur throughout the day and can be converted to other timezones for reporting purposes using FormatDateTime Instant values are always aligned to UTC. Values using other timezones are not allowed. Notice the “Z” UTC timezone designator in the example: “2016-11-29T22:35:35.674Z” The ToInstant function converts a valid string value to `instant`. The Now function returns the current system `instant` aligned to UTC.
offset_date_time	`Offset_date_time` stores date and time values with hour and minute offset information relative to UTC. Time zone name designators are not stored. Example: “2016-09-22T03:24:29+03:00” Use offset_date_time columns to store values in a table that have timezone offset values. Refer to ToOffsetDateTime to cast valid values to `offset_date_time`.
offset_time	`Offset_time` stores time values with hour and minute offset information relative to UTC. Time zone name designators are not stored. Example: “05:40:38-08:00” Use when maintaining timezone offset information is required. Refer to ToOffsetTime to cast valid values to `offset_time`. WithZOffset converts values from one offset value to another.
time	`Time` stores hour, minute, and second values with no timezone designator or offset information. Values reflect a point in time of unknown zone. Example: “05:40:38” Refer to ToTime to cast valid values to `time`.
zoned_date_time	`Zoned_date_time` stores date and time values with hour and minute offset AND time zone designator information relative to UTC. Example: “2014-12-03T10:15:30+01:00[Europe/Paris]” Use when storing values in a table column that cross multiple time zones. Refer to ToZonedDateTime for casting examples. Use the WithZoneSameInstant and WithZoneSameInstant to convert between time zone designators. ZoneId and ZoneName return zone id and name designators for a given value.