Dataframe is Offset by -1 Days From Source Data

Question

I am using a connector to query some tables in Dynamics 365 Business Central and when I view my dataframe all of my dates are offset by -1 days.

I generated a logfile for a specific invoice to use as an example, and in the logfile, I see the correct Posting_Date of "2022-04-01" and the query is completed successfully with the correct data that matches what I see in Business Cental.

Then, when I go to view the header, or load the dataframe to my PostgreSQL database, it shows as "2022-03-31." Anyone have any ideas on what might be going on here? Everything seems to look fine until I try to view my dataframe, where all records display the correct date -1.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("d365businesscentral:///?InitiateOAuth=GETANDREFRESH;company=[company name];logfile=[logfile location];verbosity=3;timeout=0;OrganizationUrl=https://api.businesscentral.dynamics.com/v2.0/[key]/[environment]/ODataV4")

df = pd.read_sql("""
    SELECT
    Posting_Date
    FROM Posted_Sales_Invoice_Lines
    where Document_No = 'PS-INV153588'
    ;""", engine)

print(df.head(5))

  Posting_Date
0   2022-03-31
1   2022-03-31
2   2022-03-31
3   2022-03-31
4   2022-03-31
[Finished in 8.805s]

Answer 1

I am not familiar with PostgreSQL databases and dataframes.

However Business Central stores DateTimes in the database as UTC timestamps, which I guess could explain the offset, given that you are using PST as the timezone when reading the data.

Dates are also stored as DateTime but with the time set to 00:00:00.