I am updating an app with a [Fines] table that has 10 pairs of Pmt-Dt-* and Pmt-Amt-* fields. The date fields are Date (not Datetime).
1) I created a new [Payments] with Fine-ID, Pmt-Dt, and Pmt-Amt fields. The new table will support online payments, so we want the new Pmt-Dt field to be Datetime to differentiate between multiple payment attempts on the same day.
2) I created a pipeline to read each Pmt-Amt-* of each record. If Pmt-Amt-* > 0, then the pipeline inserts the payment into the [Payments] table.
ISSUE: Although both tables are in the same application, the new [Payments] table dates are created with a timezone adjustment. The application is UTC-05:00 (Eastern US & Canada).
• 2012-02-02 (in original Fines table) is inserted as 2012-02-01 19:00 in the new table (5 hours earlier).
• 2018-08-08 (in original Fines table) is inserted as 2018-08-07 20:00 in the new table (4 hours earlier, during DST).
I can probably use time.delta(hours=5) and time.delta(hours=4) to update the original values, but this will require identify the exact spring-forward and fall-back dates for each year with payments.
Is there a better, more automated solution?
