PostgreSQL Date Differences

[ryan0585]

I have an unusual issue I've run into.  We have a front-end client where our users enter dates in a datetime format.  The application sits on top of a PostgreSQL database, which I'm able to connect to Power BI.  I use DBeaver as my query editor.

Lets say I have a date of "2018-03-31 20:00:00" (so 8pm on 3/31/2018).  From that date, I want to extract the month value, which would be "3", or "March".

In DBeaver, the date matches user entry values in the front end application.  Extracting the month value also gives me March.

However, in Power BI (using the Import method):

- The raw date comes in correctly (3/31/2018 8:00:00 PM) - Yay!

- The PostgreSQL logic to extract the month returns April, as if it's rounding up - Boo!

- Casting the date value to a DATE rounds the date to 4/1/2018 12:00:00 AM - Boo!

- Flooring the date (date_trunc method) returns the raw value, including the 8:00 PM time - Boo!

So, same exact query, different results.  DBeaver correctly handles all of these methods.  Why doesn't Power BI do the same when importing the data?

Thanks!

EDIT: A colleague of mine has noticed similar issues in Excel, so it doesn't appear to be just a Power BI issue.

[v-lili6-msft]

hi, @ryan0585 

I don't have PostgreSQL, Since the data is Imported, Could you share your simple sample pbix file for us have a test?

Best Regards,

Lin

[ryan0585]

Thanks Lin.  We're working through a migration this week, so (hopefully tomorrow) I'll provide a trimmed down file attempting to illustrate the issue.  That said, truly seeing what's going on requires a PostgreSQL editor to compare the output side-by-side.

In the meantime, I got around the issue by getting the month name from the date using Power Query / M, but I know that isn't always an option for folks.

Will upload a file shortly.

Thank you.

[v-lili6-msft]

hi, @ryan0585 

Could you please tell me if your problem has been solved? please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

Best Regards,

Lin

[ryan0585]

Hi Lin, sorry but the original reply was not a solution so I will not mark it as such.  I have not had time to generate a sample dataset that does not contain PII.  The issue I'm experiencing is specific to PostgreSQL data sources, so unless there's someone with access to PostgreSQL, it's going to be difficult for anyone to help me here.

My workaround was to do the date modification work I needed to do in Power Query instead of PostgreSQL.  Still, the underlying question about PostgreSQL date interpretation vs. Power BI remains, if for nothing else, curiosity on my end.