What could be the reason for the difference in date format in query results in PDO and SQL?

Question

I try to run a SELECT query using PDO

    $dbh = new PDO('connection string', 'user', 'pass'); 
    $stmt = $dbh->prepare('SELECT id, date_begin, date_end FROM v_accounts where id = 248234999'); 
    $stmt->execute();
    var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));

The result of this query is:

    array(1) {
      [0] =>
      array(3) {
        'id' =>
        string(9) "248234999"
        'date_begin' =>
        string(10) "01.01.2022"
        'date_end' =>
        string(10) "01.07.2022"
      }
    }`

If I run this query(SELECT id, date_begin, date_end FROM v_accounts where id = 248234999 ) in pgsql database console, I have another result: 248234999,0006-07-15,0007-01-12 Id is unique. v_accounts is a view, fields date_begin and date_end are created by this code: `

   to_date(date_begin::text, 'DD.MM.YYYY'::text) AS date_begin,
   to_date(date_end::text, 'DD.MM.YYYY'::text)   AS date_end

It works in php 5.6 and php8.2, Postgresql 9.6

What is the reason for the difference in query results in these two cases?

Answer 1

If you have a column of the date data type (there is no such data type as datetime in PostgreSQL) you should never do a cast to text

 date_begin::text

Why? The result depends on the setting of the parameter DATESTYLE which can be different in different sessions / clients.

Small illustration in psql

set datestyle to ISO, DMY;  
postgres=# select date'2022-07-12'::text;
    text
------------
 2022-07-12

set datestyle to SQL, MDY; 
postgres=# select date'2022-07-12'::text;
    text
------------
 07/12/2022

So you see that one and the same date literal is casted to completely different text strings.

This does not completely explains your observation, but points to the source of the problem. Simple remove the meaningless transformation of the date column to text and back to the date (that occurs surprisingly often in a code)

 to_date(date_begin::text, 'DD.MM.YYYY'::text) AS date_begin

and replace it with a simple usage

date_begin

If you want to get rid of the time component of a timestamp simple cast to date

postgres=# select now()::date;
    now
------------
 2023-04-15

If you really need to transform a date/time column to a string use to_char with an explicit format.