_sdc_batched_at may be local time

[tombriggsallego]

Describe the bug
The value stored in the _sdc_batched_at field reflects the local time of the system running target-postgres but should be UTC.

I believe the issue is simply this (line 68 in __init__.py)

extended_record['_sdc_batched_at'] = datetime.now().isoformat()

in contrast, pipelinewise-tap-mysql populates the time-extracted field using

time_extracted = utils.now()

which produces a value in UTC.

To Reproduce
Steps to reproduce the behavior:

1. Load some data with the add_metadata_columns option enabled
2. Inspect the _sdc_batched_at column - values will reflect local time

Expected behavior
Values should be in UTC.

Your environment

• Version of target: 2.1.1
• Version of python 3.10.6

[tombriggsallego]

While I think the code could still be better, I've discovered that this doesn't happen in all environments. In environments where it does happen setting the TZ environment variable to the local time zone causes the value of _sdc_batched_at to be UTC.

[datadisciple]

to expand on @tombriggsallego 's idea above....for anyone using meltano and seeing this discrepancy, it seems this can be solved within the meltano.yml by defining the TZ environment variable specific to the target-postgres plugin:

  loaders:
  - name: target-postgres
    variant: transferwise
    pip_url: pipelinewise-target-postgres
    env:
      TZ: UTC

this essentially replaces now() in the line 68 init.py call below with utcnow():

extended_record['_sdc_batched_at'] = datetime.now().isoformat()

since the TZ variable is being set to UTC only within the target-postgres loader, it doesn't seem to be causing any other issues with meltano's concept of time, but do use at your own risk as I haven't fully investigated this