SnapLogic DateTime Conversion Guidelines

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”}’))

1 Does not provide time zone support. If provided, time zone offset will be stripped.
2 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.
3 Supports time zone.

Also see Database Data Types in the documentation.

1 Like

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.

A Snowflake reference would be nice also.

TK

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).

1 Like

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