Summary
Airbyte converts timestamps to UTC timezone internally. During incremental sync, the last modified timestamp in the source is in IST format, but at the destination, it is in UTC format. Source timestamp is in TIMESTAMP type and at destination, it is in a string format due to how it is treated in the Airbyte JSON schema.
Question
Hi Airbyte team, does Airbyte internally convert timestamps to UTC timezone. Recently we noticed that during incremental sync the last modified in source is in IST format but at destination it is in UTC format. In source it is in TIMESTAMP type and at destination it is in a string format (because in airbyte json schema we are treating it as string format). Is this an expected behavior?
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.
Join the conversation on Slack
["timestamp-conversion", "utc-timezone", "incremental-sync", "json-schema"]
What type of source? In most systems, TIMESTAMP types are not time zone aware. Even something like TIMESTAMP WITH TIME ZONE in Postgres only refers to the input handling; the data is always stored as UTC, and when output is converted to the session/database TZ, not the one that was input (since in most systems this isn’t actually stored). This is the default expectation of the SQL standard (SQL99 specifically).
So typically you’d store a zone alongside the value for conversion, often using a named value so that the system can appropriately handle things like daylight saving time.
Exact conversions done between system types and Airbyte types vary, as do how connectors handle the values (e.g. some community connectors may not force UTC handling, although it’s almost always most appropriate to).