SnapLogic DateTime Conversion Guidelines

[robin]

The SnapLogic Elastic Integration Platform native DATETIME data type is time zone-aware. For service endpoints that do not expose data types that are not time zone-aware, the SnapLogic Expression Language can be used to easily convert to supported 3rd party date, datetime, time, and timestamp data types.

The matrix below provides sample conversions between the SnapLogic DATETIME variable, $snapDateTime, and several known 3rd party data types normalized to the US/Pacific time zone.

NOTE: For 3rd party DATETIME/TIMESTAMP data types that do not support time zone, the samples assume that these values are being stored normalized to the UTC (Coordinated Universal Time) time zone. In addition, links to reference documentation for 3rd party data types have been provided where applicable.

Service/Endpoint	Data Type	Sample Conversion Expression
Amazon Redshift	DATE	LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’))
Amazon Redshift	TIMESTAMP1	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
MongoDB	DATETIME3	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’))
MySQL	DATE	LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’))
MySQL	DATETIME1	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
MySQL	TIME1	LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’))
MySQL	TIMESTAMP2	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’))
Oracle	DATE	LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’))
Oracle	TIMESTAMP1	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
Oracle	TIMESTAMP WITH TIME ZONE3	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’))
Oracle	TIMESTAMP WITH LOCAL TIME ZONE	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"US/Pacific”}’))
PostgreSQL	DATE	LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’))
PostgreSQL	TIME1	LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"UTC”}’))
PostgreSQL	TIME WITH TIME ZONE3	LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’))
PostgreSQL	TIMESTAMP1	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
PostgreSQL	TIMESTAMP WITH TIME ZONE3	LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’))
SAP HANA	DATE	LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’))
SAP HANA	TIME	LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’))
SAP HANA	SECONDDATE	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
SAP HANA	TIMESTAMP1	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
Salesforce	DATE	LocalDate.parse($snapDateTime.toLocaleDateString('{“timeZone”:"US/Pacific”}’))
Salesforce	DATETIME 1	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
Salesforce	TIME	LocalTime.parse($snapDateTime.toLocaleTimeString('{“timeZone”:"US/Pacific”}’))
SQL Server	DATETIME1	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))
SQL Server	DATETIME21	LocalDateTime.parse($snapDateTime.toLocaleDateTimeString('{“timeZone”:"UTC”}’))

¹ Does not provide time zone support. If provided, time zone offset will be stripped.
² Supports time zone. Time zone is not stored interally. By default, on a SELECT, the value is normalized to the time zone set for the user’s local session.
³ Supports time zone.

Also see Database Data Types in the documentation.

[Surendar]

Thanks for your quick reference pic. It will be great to have the same kind of structure for Google Big Query and other latest database snaps if anything missed.

[tk42]

A Snowflake reference would be nice also.

TK

[del]

IMHO, it might behoove SnapLogic to produce a DateTime Library to convert to the most popular endpoints requiring specific DateTime formats. (i.e. toSqlDateTime2(), toOracleTimeStamp(), toSalesforceDate(), fromSalesforceDate(), etc.). (Also to include locale and timezone parameters).

[SandeepVemula]

@robin Is there any way to get offset also in the output Date like 2011-10-10T14:48:00.123-08:00