How to translate PHP date format to MySql, PGSql format?

Question

MySQL

Good morning, comrades.
Please help me solve the issue, in my head only some kind of bicycle translators are drawn.
I work with Yii2, I am finalizing the report manager, from the basic requirements, MySql and PGSql support is required.
Task:
There is a field in the database, such as DATETIME / DATE / TIMESTAMP (we do not know, the report designer is not tied to any particular table, the maximum can be described in the config). It is necessary to make a selection from the database using the date or strftime
style format . This is done so that dynamically calculated fields can then be used when grouping data.
Now bringing the date to the desired format is done on the PHP side, and the unification of all possible data types by fetching TIMESTAMP from the database.
That's all, do you have ideas?
-----------------------
UPD In short, you need to get the date in a specific format and still support MySql and PGSql.

Answer 1

I didn’t bother too much, I didn’t find it ready, as a result, such a bicycle with square wheels:

<?php

  $config = [
    // Day [01-31]
    'd' => [
      'mysql' => '%d',
      'pgsql' => 'DD',
    ],
    // Weekday name 3 chars [from Mon to Sun]
    'D' => [
      'mysql' => '%a',
      'pgsql' => 'Dy',
    ],
    // Full weekday name [from Sunday to Saturday]
    'l' => [
      'mysql' => '%W',
      'pgsql' => 'Day',
    ],
    // Day of year [001..366](PgSql && MySql)
    'z' => [
      'mysql' => '%j',
      'pgsql' => 'DDD',
    ],
    // Week of year (01..53)
    'W' => [
      'mysql' => '%v',
      'pgsql' => 'IW',
    ],
    // Month, numeric [01..12]
    'm' => [
      'mysql' => '%m',
      'pgsql' => 'MM',
    ],
    // Abbreviated month name [Jan..Dec]
    'M' => [
      'mysql' => '%b',
      'pgsql' => 'Mon',
    ],
    // Year, numeric, four digits
    'Y' => [
      'mysql' => '%Y',
      'pgsql' => 'YYYY',
    ],
    // Year, numeric, two digits
    'y' => [
      'mysql' => '%y',
      'pgsql' => 'YY',
    ],
    // Month name [January..December]
    'F' => [
      'mysql' => '%M',
      'pgsql' => 'Month',
    ],
  ];

  $datetime = '20160101000001';
  $originalFormat = 'd D l z W m M Y y F';

  $elements = str_split($originalFormat);
  $mySqlFormat = '';
  $pgSqlFormat = '';
  foreach($elements as $element) {
    if(array_key_exists($element, $config)) {
      $m = $config[$element]['mysql'];
      $p = $config[$element]['pgsql'];
    }
    $mySqlFormat .= ($m) ? $m : $element;
    $pgSqlFormat .= ($p) ? $p : $element;
    unset($m, $p);
  }

  echo "Original format is: {$originalFormat}\n";
  echo "MySQL format is: {$mySqlFormat}\n";
  echo "PgSQL format is: {$pgSqlFormat}\n";

Answer 2

Well, for starters, you can use DATETIME instead of TIMESTAMP, and then by sampling months and in general as you like, especially since PHP also has a very convenient SPL DateTime.