Date value changes when copied to new table with pipeline.

Question

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?

prashantmaheshw · Answer

Interested in knowing the solution, it's basically reseting the time to UTC/GMT. My account is configured for IST (UTC/GMT+5:30) ,&nbsp;Maybe&nbsp;DougHenning1 can help out&nbsp;

katlyncowley · Answer

Are your app properties set to use the billing account timezone or a specific timezone?&nbsp;&nbsp;

doughenning1 · Answer

If you add the time to the date then it should not roll back to the previous day:
{{ (a.payment_date|date_ymd) ~ "T12:00" }}
Creates a string like "2018-08-08T12:00"

erichsiemon · Answer

The latter (specific time zone). Note: I also tried with the billing account setting, but that did not alter the behavior.&nbsp;

erichsiemon · Answer

This did not work for me, but I have been unable what my mistake is.

My source tbl/fld is 01-01-2023.

My original pipeline logic displays 12-31-2022 07:00PM (EST) in the destination tbl/fld.

My revised logic displays 01-01-2023 07:00AM (EST) in the destination tbl/fld.

Forum Discussion

Date value changes when copied to new table with pipeline.