Ingesting timestamps without time zones as longs

Ask the Community
1

Data Connection’s sync configuration page allows the option to convert timestamps without time zones to long (unix time), and the description of the behavior is as follows:
“As a long: timestamps without time zones will be written into Foundry as a long value representing the number of milliseconds from the epoch of 1970-01-01T00:00:00Z. This conversion into a long value assumes the time zone based on the system that the job is running on, and cannot be assumed to be the same as the source system.”

When a user then use a Cast to Timestamp expression on the converted column in Pipeline Builder, the resulting timestamp is in UTC. The user’s expectation is that Cast to Timestamp should instead result in a timestamp in the timezone where the Foundry instance resides - “the system that the job is running on”.

Is this expected behavior? Can someone please clarify “This conversion into a long value assumes the time zone based on the system that the job is running on”?

2

The user’s expectation is that Cast to Timestamp should instead result in a timestamp in the timezone where the Foundry instance resides - “the system that the job is running on”

I disagree with this. Why should a user be expected to know where the AWS/GCP/Azure hosts are running? How could they even be sure that all hosts are within the same timezone? This maybe makes sense in an on-prem paradigm but I don’t think it tracks in the cloud (especially on an MT stack).

In ISO8601, all times are represented as an offset from UTC. If no timezone information is provided the specification allows you to assume ‘local time’, however Foundry has no way to determine the ‘local time’ as a cloud system.

If the user knows the timezone of the machine which produced the timestamp, they should just hard code this manually. In an ideal world, all timestamps should have timezone information.