1

I have a SQL DATETIME field DT that I want to feed into Kafka using Avro. DT is in local time but has no time zone; that's stored in another column TZ as an offset from UTC in minutes. It seems that an Avro timestamp-millis would be appropriate for DT, but I'm confused about conversion, given the lacking time zone. From looking at the connector source code, I think it will just default to UTC.

So that raises the questions:

  1. Will all visibly similar datetimes convert to the same number of milliseconds since the/an epoch, regardless of their nominal time zone? For example, does (2018-01-01T00:00Z).to_timestamp_ms() == (2018-01-01T00:00).to_timestamp_ms() == (2018-01-01T00:00+05).to_timestamp_ms()?

  2. More importantly, is it possible to convert to true UTC after ingestion by subtracting the time zone offset TZ from the datetime field DT (which is now in milliseconds since some epoch)? In other words, if the connector incorrectly assumes UTC, and falsely interprets the datetime as UTC, can the true datetime be recovered after that by subtracting the offset?

Details on transformation steps

I think the order of operations is something like this on the Connector (largely out of my control):

tz = read_field_as_int('tz')
dt = read_field_as_string('dt')
parsed_datetime = datetime(dt, timezone=UTC)
message = {
    'dt': parsed_datetime.to_timestamp_ms(),
    'tz': tz
}
producer.produce(message)

And then later, in the consumer, maybe this would work?

ms_per_min = 60 * 1000
message = consumer.poll()
true_timestamp = message['dt'] - message['tz'] * ms_per_min
true_dt = datetime.from_timestamp(true_timestamp, timezone=UTC)
Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
z0r
  • 8,185
  • 4
  • 64
  • 83
  • 1
    1. No. If using the Unix/Java epoch, your first sample correspond to 1 514 764 800 000, the second depends on `TZ` and the third corresponds to 1 514 746 800 000 milliseconds (note that 4 and 6 are swapped compared to the first sample). 2. Yes, it is possible, but we need to know if `TZ` is in hours, minutes or seconds. – Ole V.V. Feb 07 '19 at 11:26
  • @OleV.V. Interesting, thanks. Let's say `TZ` is in minutes. – z0r Feb 07 '19 at 12:00
  • 2
    Are you talking about a specific Kafka Connect connector here? e.g. the JDBC Source connector? – Robin Moffatt Feb 07 '19 at 12:32
  • For precision you may give an example database row and desired result for that row? You may also use an online epoch converter, for example [Epoch & Unix Timestamp Conversion Tools](https://www.epochconverter.com). – Ole V.V. Feb 11 '19 at 10:50

0 Answers0