SnapLogic DateTime Conversion Guidelines
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-25-2017 02:56 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-01-2017 06:29 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-01-2017 08:34 AM
A Snowflake reference would be nice also.
TK
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-05-2018 11:06 AM
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-04-2018 01:16 AM
@robin Is there any way to get offset also in the output Date like 2011-10-10T14:48:00.123-08:00