Formatting dates output

PatRoy · September 28, 2020

Hi,

I'm trying to achieve a special output of dates. I essentially have an array of dates like so:

// dates in yyyy-mm-dd format
$dates = array('2005-05-21', '2006-11-01', '2006-11-02', '2020-09-28', '2020-09-29', '2020-09-30', '2020-10-01');

I need to output theses dates like so:

05 may 2005
01, 02 november 2006
28, 29, 30 september 2020
01 october 2020

I'm really not sure how to go about this. I'm sure this must be possible. Any help would be great!!

Thanks a bunch!

Pat

Strider64 · September 28, 2020

Checkout https://www.php.net/manual/en/datetime.format.php it should get you on the right track.

gizmola · September 28, 2020

Here is a simple example, turning the date strings into DateTimes and then using format to output the date. You will need to read the manual page Strider64 provided to reference all the different ways you can specify output. There are also a number of predefined constants you can use.

<?php

$dates = array('2005-05-21', '2006-11-01', '2006-11-02', '2020-09-28', '2020-09-29', '2020-09-30', '2020-10-01');

foreach ($dates as $date) {
    $dt = DateTime::createFromFormat('Y-m-d', $date);
    
    echo $dt->format('r');
    echo PHP_EOL;
    echo $dt->format('\i\t \i\s \t\h\e jS \d\a\y.');
    echo PHP_EOL;
    echo $dt->format(DateTimeInterface::ATOM);
    echo PHP_EOL;
    echo PHP_EOL;
}

Your dates all need to be valid, or you may get some odd results. It is possible to check as to whether or not a date was invalid, which is documented on the manual page for Datetime::createFromFormat(), if that is a possibility you need to handle.

Barand · September 28, 2020

Sort the array then loop through your array of dates.

Store them in another array whose key is "month year" and the elements are arrays of days. EG

[ 'January 2020' => [1, 22, 31],
  'March 2020'   => [10, 12]
  ]

Loop through that array outputting the imploded day arrays and keys.

edit:

PS if your dates are a database table

mysql> SELECT DATE_FORMAT(datecol, '%M %Y') as my
    ->                          , GROUP_CONCAT(day(datecol) SEPARATOR ', ') as days
    ->                     FROM datetbl
    ->                     GROUP BY DATE_FORMAT(datecol, '%M %Y')
    ->                     ORDER BY datecol;
+----------------+------------+
| my             | days       |
+----------------+------------+
| May 2005       | 21         |
| November 2006  | 1, 2       |
| September 2020 | 28, 29, 30 |
| October 2020   | 1          |
+----------------+------------+

Edited September 28, 2020 by Barand

gizmola · September 28, 2020

I missed the subtlety in your question. Barand has a good solution.

PatRoy · September 28, 2020

Thank you folks!!

I was thinking of Barand's solution and that's what I adopted. I was really tempted to use the SQL way also, but I also need all my dates separately (they are all in a hidden dates[] array for modification with a calendar. I'd have to use 2 sql statements if I wanted just the SQL solution.

This is my code :

<?php 
$dates = array ( '2005-05-21', '2020-09-29', '2006-11-02', '2020-09-28', '2020-09-30', '2020-10-01', '2006-11-01' );
sort($dates); // sort oldest to newest

$my_dates = [ ];

// re-generate proper dates array [year][month] => array(days...)
foreach ( $dates as $d )
{
  $date = new DateTime($d);

  $year = $date->format('Y');
  $month = $date->format('F');
  $day = $date->format('d');

  if ( ! isset($my_dates[$year][$month]) )
  {
    $my_dates[$year][$month] = array ();
  }

  array_push($my_dates[$year][$month], $day);
}

// pretty-print the dates
foreach ( $my_dates as $year => $monthDays )
{
  $prettyDate = '';

  foreach ( $monthDays as $month => $days )
  {
    foreach ( $days as $day )
    {
      $prettyDate .= "$day, ";
    }

    $prettyDate = substr($prettyDate, 0, -2) . ' '; // remove last ',' after days   
    $prettyDate .= $locale->_($month) . " $year <br/>"; // print month and year, with translated month
  }

  echo $prettyDate;
}
?>

Outputs:

21 Mai 2005
01, 02 Novembre 2006
28, 29, 30 Septembre 2020
01 Octobre 2020

Thanks a bunch for your help and time!
Pat

Edited September 28, 2020 by PatRoy

PatRoy · September 29, 2020

16 hours ago, Barand said:


mysql> SELECT DATE_FORMAT(datecol, '%M %Y') as my
    ->                          , GROUP_CONCAT(day(datecol) SEPARATOR ', ') as days
    ->                     FROM datetbl
    ->                     GROUP BY DATE_FORMAT(datecol, '%M %Y')
    ->                     ORDER BY datecol;
+----------------+------------+
| my             | days       |
+----------------+------------+
| May 2005       | 21         |
| November 2006  | 1, 2       |
| September 2020 | 28, 29, 30 |
| October 2020   | 1          |
+----------------+------------+

Say Barand, I've adopted your SQL way in other pages, which I find is great! Wondering though : any idea if MySQL would be able to output the days separated by an hyphen '-' for continuous numbers, and a comma for others ?
i.e.

+--------------+-----------+------+
| days         | month     | year |
+--------------+-----------+------+
| 8, 10-12, 16 | August    | 2020 |
| 20, 27-30    | September | 2020 |
| 1-2          | October   | 2020 |
+--------------+-----------+------+

Cheers! Pat

Barand · September 29, 2020

SQL could, but it would be a huge PITA using user variables to keep track of year and month changes and contiguous day ranges. Far easier to do that bit in the PHP ...

##
##  SET UP SOME TEST DATES
##

$dates = array( '2020-08-10', '2020-08-11', '2020-08-12', '2020-08-16', '2020-08-08', 
                '2020-09-20', '2020-09-27', '2020-09-28', '2020-09-29', '2020-09-30', 
                '2020-10-01', '2020-10-02' );

$db->exec("CREATE TEMPORARY TABLE dates (datecol date)");

$stmt = $db->prepare("INSERT INTO dates (datecol) VALUES (?)");
foreach ($dates as $d) $stmt->execute([$d]);


##
##  PROCESS THE DATES
##

$res = $db->query("SELECT  DATE_FORMAT(datecol, '%M %Y') as my
                         , GROUP_CONCAT(day(datecol) ORDER BY day(datecol) SEPARATOR ', ') as days
                    FROM dates
                    GROUP BY my
                    ORDER BY datecol 
                    ");
foreach ($res as $r) echo  contiguise($r['days']) . " {$r['my']}<br>";

/**
* replace contiguous ranges with "-"
* 
* @param mixed $list  comma-space separated
*/
function contiguise($list)
{
    $nums = explode(', ', $list);
    $new = '';
    $prev = -1;
    $contig = 0;
    foreach ($nums as $k => $n) {
        if ($n == $prev + 1) {
            $contig = 1;
        }
        else {
            if ($contig) {
                $new .= "-{$prev}" ;
                $contig = 0;
            }
            if ($prev != -1) {
                $new .= ', ';
            }
            $new .= $n;
        }
        $prev = $n;
    }
    if ($contig)  $new .= "-{$prev}" ;
    return $new;
}

... giving us ...

8, 10-12, 16 August 2020
20, 27-30 September 2020
1-2 October 2020

Edited September 29, 2020 by Barand
spwlling errors corrected

Barand · October 1, 2020

For the record (several user variables, three levels of subquery and a user-defined function later) here is an SQL impementation.

The query on it's own, without the function, gave

+----------------+----------------+
| my             | days           |
+----------------+----------------+
| May 2005       | 21             |
| June 2005      | 22             |
| November 2006  | 1-2            |
| September 2019 | 28-29-30       |
| October 2019   | 1              |
| August 2020    | 8 10-11-12 16  |
| September 2020 | 20 27-28-29-30 |
| October 2020   | 1-2            |
+----------------+----------------+

The contiguise() function parses the "days" string, adds commas and removes the inner numbers from the ranges and replaces them with a "-". With the function it gives

+----------------+--------------+
| my             | days         |
+----------------+--------------+
| May 2005       | 21           |
| June 2005      | 22           |
| November 2006  | 1-2          |
| September 2019 | 28-30        |
| October 2019   | 1            |
| August 2020    | 8, 10-12, 16 |
| September 2020 | 20, 27-30    |
| October 2020   | 1-2          |
+----------------+--------------+

QUERY

SELECT my 
     , contiguise(REPLACE(GROUP_CONCAT(contig order by dno separator ' '), ' - ', '-')) AS days
FROM (
        SELECT CASE WHEN dno = @prev + 1 AND my = @prevmy
                    THEN concat('- ',dno)
                    ELSE dno
                    END AS contig
             , date
             , @prev := dno AS dno
             , @prevmy := my AS my
        FROM (
                SELECT DATE_FORMAT(date, '%M %Y') AS my
                     , DAY(date) AS dno
                     , date
                FROM date
                ORDER BY date
             ) pre  
             JOIN (SELECT @prev := -1, @prevmy := '') init
     ) data
GROUP BY my
ORDER BY date;

FUNCTION

DELIMITER $$
CREATE FUNCTION `contiguise`(days varchar(150) ) RETURNS varchar(150) CHARSET utf8
BEGIN
    DECLARE temp varchar(150) DEFAULT '';
    DECLARE result varchar(150) DEFAULT '';
    DECLARE pos1 int DEFAULT 1;
    DECLARE pos2 int DEFAULT 0;
   
    WHILE pos1 <= LENGTH(days) DO
		SET pos2 = LOCATE(' ', days, pos1); 
		IF pos2 = 0 THEN 
			SET pos2 = LENGTH(days)+1;
		END IF;
        SET temp = SUBSTRING(days, pos1, pos2 - pos1);
        IF LOCATE('-', temp) <> 0 THEN 
			SET temp = CONCAT(SUBSTRING_INDEX(temp, '-',1), '-', SUBSTRING_INDEX(temp, '-',-1));
		END IF;
        IF result = '' THEN
			SET result = temp;
		ELSE
			SET result = CONCAT(result, ', ', temp);
		END IF;
        SET pos1 = pos2 + 1;
    END WHILE;
    
    RETURN result;
END$$
DELIMITER ;

TEST DATA

+------------+
| date       |
+------------+
| 2005-05-21 |
| 2005-06-22 |
| 2006-11-01 |
| 2006-11-02 |
| 2019-09-28 |
| 2019-09-29 |
| 2019-09-30 |
| 2019-10-01 |
| 2020-08-08 |
| 2020-08-10 |
| 2020-08-11 |
| 2020-08-12 |
| 2020-08-16 |
| 2020-09-20 |
| 2020-09-27 |
| 2020-09-28 |
| 2020-09-29 |
| 2020-09-30 |
| 2020-10-01 |
| 2020-10-02 |
+------------+

Sign In

Formatting dates output

Recommended Posts

PatRoy

Link to comment

Share on other sites

Strider64

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

gizmola

Link to comment

Share on other sites

PatRoy

Link to comment

Share on other sites

PatRoy

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information