I’m in agreeance with @knservis here - you should be keeping collector_tstamp in UTC time and not local time.
At the moment the Scala stream collector uses
System.currentTimeMillis as the collector_tstamp for an event and although it’s possible to modify this there are several reasons not to including
- You would also have to make reference changes to anywhere bad rows are being collected (where failure_tstamp is in UTC)
- Certain enrichments (e.g., currency conversion) rely on UTC timestamps
- Local time is more difficult to reason about - the current schema for atomic.events sets collector_tstamp to
TIMESTAMP rather than
TIMESTAMPTZ. Local time doesn’t always progress forward at 1 second per second (time offsets change) whereas this is mostly guaranteed with UTC.
- The collector_tstamp doesn’t help much in determining when an event occurred as it’s only indicative of when an event was received by a collector. @alex has written a comprehensive blog post on how time is calculated and processed and what timestamps are appropriate to use in what circumstances.
If you do want to convert the collector_tstamp to local time you can do this in most databases as @knservis has mentioned by using CONVERT_TIMEZONE e.g.,
CONVERT_TIMEZONE('Timezone/Example', collector_tstamp). It’s easy to add this as part of the data modelling Redshift that runs every ETL run using something like SQL runner or any other automation tool.