Cashier attempting to write ends_at in invalid datetime due to Daylight Savings

[emergingdzns]

Cashier Paddle Version

2.5.6

Laravel Version

11.41.3

PHP Version

8.3

Database Driver & Version

MySQL 8 on Amazon RDS

Description

Our db and servers are set for America/New_York timezone.

We are getting the error below:

 Illuminate\Database\QueryException · SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2025-03-09 02:35:01' for column 'ends_at' at row 1 (Connection: erank_main, SQL: update `subscriptions` set `ends_at` = 2025-03-09 02:35:01, `subscriptions`.`updated_at` = 2025-02-09 20:16:28 where `id` = 107213)

But the time that's getting written doesn't exist because it jumps from 2am to 3am on March 9, 2025.

Since this is getting written by the Laravel Cashier system, we have no way to fix this short of reconfiguring our db to allow invalid dates, which isn't a solution.

Steps To Reproduce

Send the following payload to the paddle/webhook endpoint:

{
   "payload":{
      "data":{
         "id":"sub_01jkmxxxxxxxxxxxxxxxxx",
         "items":[
            {
               "price":{
                  "id":"pri_01j1vxxxxxxxxxxxxxxxx",
                  "name":"Monthly",
                  "type":"standard",
                  "status":"active",
                  "quantity":{
                     "maximum":1,
                     "minimum":1
                  },
                  "tax_mode":"account_setting",
                  "seller_id":"1999999",
                  "created_at":"2024-07-03T02:03:15.051183Z",
                  "product_id":"pro_01j1xxxxxxxxxxxxxxxx",
                  "unit_price":{
                     "amount":"599",
                     "currency_code":"USD"
                  },
                  "updated_at":"2024-10-15T21:43:35.616803Z",
                  "custom_data":null,
                  "description":"Monthly Plan",
                  "import_meta":null,
                  "trial_period":null,
                  "billing_cycle":{
                     "interval":"month",
                     "frequency":1
                  },
                  "unit_price_overrides":[
                     {
                        "unit_price":{
                           "amount":"550",
                           "currency_code":"EUR"
                        },
                        "country_codes":[
                           "NL"
                        ]
                     },
                     {
                        "unit_price":{
                           "amount":"50400",
                           "currency_code":"INR"
                        },
                        "country_codes":[
                           "IN"
                        ]
                     }
                  ]
               },
               "status":"active",
               "product":{
                  "id":"pro_01jxxxxxxxxxxxxxxxxx",
                  "name":"eRank Basic",
                  "type":"standard",
                  "status":"active",
                  "image_url":"https:\/\/example.com\/img\/icon.png",
                  "seller_id":"199999",
                  "created_at":"2024-07-03T01:57:04.882Z",
                  "updated_at":"2024-11-20T20:21:03.457Z",
                  "description":"Basic Plan",
                  "tax_category":"standard"
               },
               "quantity":1,
               "recurring":true,
               "created_at":"2025-02-09T02:35:01.416Z",
               "updated_at":"2025-02-09T02:35:01.416Z",
               "trial_dates":null,
               "next_billed_at":null,
               "previously_billed_at":"2025-02-09T02:35:01.112303Z"
            }
         ],
         "status":"active",
         "discount":null,
         "paused_at":null,
         "address_id":"add_01xxxxxxxxxxxxxxxxxxxxx",
         "created_at":"2025-02-09T02:35:01.416Z",
         "started_at":"2025-02-09T02:35:01.112303Z",
         "updated_at":"2025-02-09T04:24:37.372Z",
         "business_id":null,
         "canceled_at":null,
         "custom_data":null,
         "customer_id":"ctm_01xxxxxxxxxxxxxxxxxxxxxxx",
         "import_meta":null,
         "billing_cycle":{
            "interval":"month",
            "frequency":1
         },
         "currency_code":"USD",
         "next_billed_at":null,
         "billing_details":null,
         "collection_mode":"automatic",
         "first_billed_at":"2025-02-09T02:35:01.112303Z",
         "scheduled_change":{
            "action":"cancel",
            "resume_at":null,
            "effective_at":"2025-03-09T02:35:01.112303Z"
         },
         "current_billing_period":{
            "ends_at":"2025-03-09T02:35:01.112303Z",
            "starts_at":"2025-02-09T02:35:01.112303Z"
         }
      },
      "event_id":"evt_01jxxxxxxxxxxxxxxxxxxxxxxx",
      "event_type":"subscription.updated",
      "occurred_at":"2025-02-09T04:24:38.173566Z",
      "notification_id":"ntf_01xxxxxxxxxxxxxxxxxxxxxxx"
   }
}

Mysql will throw the error if your db is in a timezone that recognizes daylight savings time:

 Illuminate\Database\QueryException · SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2025-03-09 02:35:01' for column 'ends_at' at row 1 (Connection: erank_main, SQL: update `subscriptions` set `ends_at` = 2025-03-09 02:35:01, `subscriptions`.`updated_at` = 2025-02-09 20:16:28 where `id` = 107213)

[driesvints]

Err I'm not sure what we should do here. This is a datetime we receive from Paddle. 2024-07-03T02:03:15.051183Z isn't something we control. What should we change it to? 1am? 3am? I don't feel we should be responsible for controlling that. Paddle should not send invalid datetimes.

Could you contact Paddle about this?

[emergingdzns]

I'll contact them but they will tell us that their webhooks come in in UTC timezone. But if we have our db set to ET something somewhere needs to convert the date. I would think that Cashier would convert UTC to the server/db timezone. Since we can't modify your code due to being under repo, we can't make any adjustments or intercept the webhook data prior to being written.

[crynobone]

You can create an Eloquent Observer and convert the datetime via saving() event.

[driesvints]

I would think that Cashier would convert UTC to the server/db timezone.

Nope, that's not the responsibility of this library. You'll have to do that yourself if you need that. Try the solution @crynobone offered.