Ordering by Datetime Field

[Pete]

I'm currently trying to grab pages using different templates into an array to sort by a common field - publish_date - which is a datetime field.

Here's the first part of my code:

$day = 86400;
$weekdays = $day*5;
$fortnight = $day*14;
$limit = 4;

$featured = new PageArray();

$featured->import($pages->find('template=article, publish_date>' . (time()-$fortnight) . ', images.count>0, summary!=, sort=-publish_date, sort=-id, outputFormatting=false, limit=' . $limit));

So as you can see, I'm grabbing all pages with the template "article" added in the last fortnight. In another line further down I add items using a "news" template to it that are posted in the last 5 days.

The problem is that I've put this code into my homepage template and sorting by publish date descending isn't working - this is because by the time it gets to the template the dates have already been converted from a timestamp to a human-readable time so it's displaying in a weird order (I suspect it thinks that 30 March is higher than 1 April as it only sees the number when doing this comparison, as it will have no clue what the date is now it's not a timestamp).

The solution was to put it into a module that runs before page render, but it would be nice to have an optional flag to add to the $featured->import to set outputFormatting off for that set of selected pages, or even better just retain the unformatted date as publish_date_original or something.

Not sure what the best way to go about this is, but I'm sticking with my module now as it works well there - this topic might be useful in case anyone runs into a similar situation though.

[Soma]

Have you made your date field "autojoin"? Without setting it you cant sort by the field.

On a side note, formatted dates can be sorted using "Y-m-d". For example in a sortable table.

[Pete]

Hmm... it seems to be working now without setting autojoin (I forgot to change the field on my live copy but changed it locally and they both display correctly now) so not sure what I was doing wrong before.

Oh well!

[joshuag]

I am having this exact problem. I am sorting by a datetime field that I created that has output formatting... And yes my field is auto joined. It sorts fine normally, only sorts wrong after I have imported more posts to the page array.

[joshuag]

here is what I did. I removed the output formatting on my field in the admin under fields > date Output Format > none.

Then in my template I just formatted the date with the PHP date() method like so:

<?=date('F j, Y',$post->publish_date)?>

(note: $post->publish_date is a custom field I added.)

Hope this helps someone.

[Pete]

Thanks Joshua - I've still experienced this on occasion on one site. Same conditions as you - reading pages into a page array then sorting by a date field.

Most of the time it works fine, but I've size learned to do an additional sort of -id which is fine in most cases but not ideal for queuing posts up for future publication.

Every so often I see pages display in a weird order, like the sort hasn't sorted properly on one page load, but then later on its fine.

Most odd - I'll try your suggestion and see what happens. I suspect nobody else has come across this as you have to have a very specific set of circumstances for it to happen.

[ryan]

Thanks Joshua - I've still experienced this on occasion on one site. Same conditions as you - reading pages into a page array then sorting by a date field.

There has been a recent change in behavior here (a couple weeks ago), though not one that affect you unless you are trying it for the first time, as there was nothing worthwhile in the previous behavior. PageArray::sort() used to sort by the property value without regard to the page's outputFormatting state. This would mean that it could sort by the formatted date, which would be alpha sorting (of the date string) rather than date sorting. That's really not what you want. So I recently modified it so that it always sorts by the unformatted value, regardless of whether output formatting is on. Meaning, $pageArray->sort('some_date_field'); and $pages->find('..., sort=some_date_field'); should always be consistent. Please let me know if you are up-to-date with your PW version and still experiencing incorrect behavior with this.

[joshuag]

@Ryan - This is awesome! I will update my PW and do some tests. Man you are fast Ryan... I am starting to think you have clones. hahaha

[celfred]

Please let me know if you are up-to-date with your PW version and still experiencing incorrect behavior with this.

Well... I am on 2.2.3 version (I guess up to date), and my code

$selected = $page->children()->find("createdUser=$user, sort=-date"); // HERE'S THE DATE PROBLEM
 //if ($page->children()->find("createdUser=$user")->count() > 0) {
 if ($selected.length > 0) {
   $out .= '<ul id="list-invit">';
   foreach ($selected as $invit) {
    $out .= '<li data-id="'. $invit->id .'">';
    $out .= 'Invitation le ';
    $out .= $invit->date .'<br />';
    $out .= 'Liste des invités : ';
    $out .= '<a href="#" class="guest">'. $invit->title .'</a>';
    $out .= '<a href="#" class="mod_invit">Modifier</a>';
    $out .= '<a href="../ajax/delete_invit.php" class="del_invit">Supprimer</a>';
    $out .= '';
    $out .= '';
    $out .= '</li>';
  //}
   }
   $out .= '</ul>';

doesn't work as expected (from me...) I try to get the list of pages from the logged in user and sort them along the date Datetime field. My 'date' field is formated 'Sunday, 5 November, 2012', and it seems to me that the sort order stays based upon the days alphabetical order. In other words, Wednesdays display first (because of the W) and then I get Thursdays, then Fridays... (except I'm trying to sort chronologically although I want to display the day's names).

Do I do anything wrong?

Thanks for the help ;-)

(I hope I was clear enought in what I wrote...)

[ryan]

I think that the problem did exist in 2.2.3. The current version is 2.2.9. I bet if you upgrade to it the issue will go away. Let me know how it works for you.

[celfred]

Sorry about that... I am also discovering 'git' and I thought 'git fetch' was getting the last version. In fact, I used 'git pull' and now I have 2.2.9 and... Everything works as expected!

Thanks a lot!

[thetuningspoon]

If I want to compare datetimes, what format do I need to use in my selector?

I'm trying to create a list of child pages that contains only pages with a program_start_date before April 1st.   $programs->find("program_start_date<2013-04-1") doesn't work, however.

        // Get all the child pages that use the program template and sort them by the program start date
	$programs = $page->children('template=program, sort=program_start_date, sort=title');
	?>
	
	<h3>Winter <?= $year ?></h3>
	<ul>
	<?
	foreach($programs->find("program_start_date<2013-04-1") as $program) {
	?>
		<li><?= $program->program_start_date ?> - <?= $program->title ?></li>
	<?
	}
	?>
	</ul>

[Soma]

You need a timestamp!

[thetuningspoon]

Only 8 minutes later and I have a solution!

Using the php function strtotime I can set my date in the MM/DD/YYYY format and have it automatically converted to a timestamp for use in the selector.

...
$seasonEnd = strtotime("04/01/$year");

foreach($programs->find("program_start_date<$seasonEnd") as $program) {
...

You need a timestamp!

I thought so, but I didn't know how to make one!

[Soma]

You found the right one!

[ryan]

$seasonEnd = strtotime("04/01/$year")

foreach($programs->find("program_start_date<$seasonEnd") as $program) {

The strtotime call isn't technically necessary. You could also just do this:

foreach($programs->find("program_start_date<04/01/$year") as $program) {

[thetuningspoon]

Oh cool, good to know!

[joe_ma]

Hello

Same problem here. I have got this code in a template building a table:

$events = $pages->find("template=event_date, include=hidden, sort=datum, sort=zeit"); //get all the events to display in the table
	foreach ($events as $e) {
		$datum = date("d.m.", $e->getUnformatted("datum")); // display the date as e.g. 25.12.
		$wota = date("w", $e->getUnformatted("datum")); // for weekdays
		$tage = array("So, ","Mo, ","Di, ","Mi, ","Do, ","Fr, ","Sa, ");
		$tag = $tage[$wota];
		$content .= '<tr><td class="tag">{$tag}</td><td class="datum">{$datum}</td><td class="zeit">{$e->zeit}</td><td class="vorstellung"><a href='{$e->parent->url}'>{$e->parent->Programmtitel}</a>';

the fields "datum" and "zeit" are datetime fields that are set to output "25.10.2016" and "22:00" respectively. Both of them have autojoin checked. The output of the code is as intended:

Fr, 06.11.  18:30 "Title-of-the-event"

BUT: Sorting is not correct.

PW 2.8.22

 

[LostKobrakai]

If you're using a extra time field rather use FieldtypeTime and not a datetime field. A datetime field will not only save the hour/minutes, but also the date (it defaults to today when saving the first time), so it's sorting by the whole date, which you only chose not to display and not only by the hour/minutes.

[joe_ma]

Sorry for being stupid: I cannot find a fieldtype Time …

[LostKobrakai]

It's a third party fiedltype: http://modules.processwire.com/modules/inputfield-time/

[joe_ma]

Ah, thank you.

But anyway: if I change

$events = $pages->find("template=event_date, include=hidden, sort=datum, sort=zeit")

to

$events = $pages->find("template=event_date, include=hidden, sort=datum")

Sorting is not correct either.

And since I have stored the correct date also with the "zeit" field, couldn't I sort it like this

$events = $pages->find("template=event_date, include=hidden, sort=zeit")

But all of these solutions dont sort correctly.

[joe_ma]

OK, I tested it with the FieldtypeTime. But nothing changed.

[joe_ma]

So I finally found the culprit: jqery TabeSorter.

Without TableSorter, the table is sorted correctly.

Thanks a lot for your help.