Forum Discussion

ErichSiemon's avatar
ErichSiemon
Qrew Member
8 months ago

Date value changes when copied to new table with pipeline.

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?

  • Interested in knowing the solution, it's basically reseting the time to UTC/GMT. My account is configured for IST (UTC/GMT+5:30) , 

    Maybe DougHenning1 can help out 

  • katlyncowley's avatar
    katlyncowley
    Qrew Assistant Captain

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

     

    • ErichSiemon's avatar
      ErichSiemon
      Qrew Member

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

       

  • DougHenning1's avatar
    DougHenning1
    Community Manager

    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's avatar
      ErichSiemon
      Qrew Member

      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.