Jump to content
BitPoet

FieldtypeDatetimeAdvanced - subfield selectors for date/time

Recommended Posts

Due to a discussion here in the forums, I was inspired to finally have a take on datetime fields and see if I couldn't get them to be searched a little more conveniently.

Here's a small module - still in alpha state, but I'd be happy to get some feedback - that allows searching for individual components of a date like year, month, day, hour or even day_of_week or day_of_year, and also returning them.

Github repo: DatetimeAdvanced

Current version: 0.0.5

Tested in: ProcessWire 2.8 + 3.0

Possible subfields:

  • day
  • month
  • year
  • hour
  • minute
  • second
  • day_of_week
  • day_of_year
  • week_of_year

Examples:

// Database search:
$pagelist = $pages->find("mydatefield.year=2016");

// Filtering PageArray in memory:
$maypages = $pagelist->filter("mydatefield.month=5");

// Back to our starting point:
$start = date('z');
$end = $start + 7;
$sevendays = $pages->find("mydatefield.day_of_year>=$start, mydatefield.day_of_year<$end");

// Nice side effect: subfields are now directly accessible
$blogentry = $pages->get('blog-entry-1');
echo $blogentry->title . "(" . $blogentry->publishdate->year . ")";

// New in 0.0.4: shorthand methods
echo $blogentry->publishdate->strftime("%Y-%m-%d %H:%M:%S") . PHP_EOL;
echo $blogentry->publishdate->date("Y-m-d H:i:s") . PHP_EOL;

ToDos for the future:

  • See if there's a possibility to specify ranges more conveniently
  • Check if this can perhaps wiggle its way into the PW core

Changes: example for direct subfield access and shorthand methods to strftime() and date() added.

  • Like 20

Share this post


Link to post
Share on other sites

Just a short update: if MySQL (or the OS it's running on) and PHP are configured with different time zones, results will be wrong. You'll likely not notice anything off on local dev environments where everything is configured with the same time zone, but to prevent issues on deployment, MySQL needs to be made timezone-aware and PW's database connections need to tell MySQL which timezone to use for conversions, which should IMHO be done through a not-yet-existing $config property. That's why I've filed feature request #19.

I'm now holding my fingers crossed and waiting to hear if it gets considered. This will mean that, assuming it does, the module will require at least the PW version that gets the new $config property.

  • Like 9

Share this post


Link to post
Share on other sites

@BitPoet, what do you think about changing from SQL timezone support being a strict requirement for installation to it being a strong suggestion?

My shared hosting server doesn't have timezone data but if I comment out the WireException in the install() method then things seem to work okay. The field values are saved with the correct date and time in the database and I can match pages as expected. Not sure if that means that the server's SQL and PHP are set to the same timezone (not sure how to test that).

The only issue I noticed is that with the "Default to today's date?" option selected in the field config the field shows an invalid date before a date has been entered:

2018-10-27_120037.png.7389ee5650e93fbbffd497024c688574.png

Would that be due to the missing timezone support?

  • Like 2

Share this post


Link to post
Share on other sites
13 hours ago, Robin S said:

what do you think about changing from SQL timezone support being a strict requirement for installation to it being a strong suggestion?

I actually came to that conclusion too. The latest release outputs an error message but doesn't throw an exception anymore. There  are also instructions on the module config page how to set the correct offset through dbInitCommand in site/config.php so the global timezone in the MySQL server doesn't have to match that in PHP.

13 hours ago, Robin S said:

The only issue I noticed is that with the "Default to today's date?" option selected in the field config the field shows an invalid date before a date has been entered

I'm going to look into that one.

  • Like 2

Share this post


Link to post
Share on other sites
16 hours ago, Robin S said:

The only issue I noticed is that with the "Default to today's date?" option selected in the field config the field shows an invalid date before a date has been entered

I couldn't replicate the issue here. Can you give some specifics about your setup (date/time output format, PHP version, PW version)?

Normally, DatetimeAdvanced should behave just like a regular Datetime field in that regard since it too delegates the formatting stuff to the WireDateTime class, but I might of course have missed some special cases.

Share this post


Link to post
Share on other sites
On 10/27/2018 at 12:03 PM, Robin S said:

The only issue I noticed is that with the "Default to today's date?" option selected in the field config the field shows an invalid date before a date has been entered

I could reproduce this locally too with DatetimeAdvanced v1.0.0, PW 3.0.115, PHP 7.1.

It seems that when a new page is created (after the first step of Add Page) the field gets a value of 0000-00-00 00:00:00 in the database. This gets converted to timestamp -62170025400 which then does not pass the conditional that sets the inputfield value to the current date/time.

In contrast, the core Datetime field does not set a value in the database until one is saved.

2018-10-28_160400.png.39908c04c77e3ac8f76bed7b3819a0f9.png

DatetimeAdvanced:

2018-10-28_160315.png.523e9cbb1d1ca7e0b7ad8b09435fb826.png

Datetime:

2018-10-28_160337.png.91fda37195b12231886f424b77452246.png

Share this post


Link to post
Share on other sites

This looks awesome! I was considering a similar advanced DateTime module that would store the UTC offset in addition to the timestamp, so in the UI you could select the date, time, and timezone. I might try this out and see if I can extend it for that purpose.

  • Like 2

Share this post


Link to post
Share on other sites
On 11/9/2018 at 9:48 PM, gRegor said:

store the UTC offset in addition to the timestamp, so in the UI you could select the date, time, and timezone. I might try this out and see if I can extend it for that purpose.

I guess it should be doable by extending WireDT accordingly and implementing fitting getDatabaseSchema and savePageField/loadPageField methods as well as making sleepValue/wakeupValue with arrayish data instead of plain strings, a lot like the Events fieldtype but without the multiple value (EventsArray) stuff.

On 10/28/2018 at 4:05 AM, Robin S said:

I could reproduce this locally too with DatetimeAdvanced v1.0.0

Meanwhile reproduced and working on a solution. Should be ready in a few days at most.

Meanwhile, I haven't been completely lazy:

DatetimeAdvanced v1.0.3 supports custom subfields and works together with Lister's subfield selectors.

  • Like 4

Share this post


Link to post
Share on other sites

@Robin S: since all good things take a while, I have committed a tentative fix for the "default to today" issue into the dev branch. Perhaps you could give it a spin and let me know if this works for you.

  • Like 1

Share this post


Link to post
Share on other sites

@BitPoet, thanks for the update, but sorry to report the issue with "default to today" remains for me with new pages. I tested with the dev branch of the module.

Edit: I've just noticed that it doesn't make a difference if "default to today" is selected for the field - the issue occurs regardless. Not sure if this was always the case and I just failed to check this when I originally reported it.

2019-03-09_120642.png.753719d3c6c2a698f983934cf26f010d.png

Database:

2019-03-09_120633.png.272e5e904ba863902c5cde6050e86cd6.png

Share this post


Link to post
Share on other sites

@BitPoet, in case it helps, it seems that the relevant difference between a standard DateTime field and DateTimeAdvanced is that the standard field passes this test in Fieldtype.php for new pages...

// if the value is the same as the default, then remove the field from the database because it's redundant
if($value === $this->getBlankValue($page, $field)) return $this->deletePageField($page, $field); 

...but DateTimeAdvanced does not, resulting in an empty datetime value being stored in the DB.

 

Share this post


Link to post
Share on other sites

Hi, I just stumbled upon your module. Great idea!

Your obligatory settings string

$config->dbInitCommand = "SET NAMES '{charset}', time_zone = '+02:00' ";

is not considering summer time changes? Maybe this can be calculated from $config->timezone = 'Europe/Berlin' (or whatever), a setting which is there already in config?

Uninstalling the Advanced Datetime field  leaves Advanced Datetime Inputfield and Datetime Extensions installed. Is this intended?

Share this post


Link to post
Share on other sites
On 10/5/2019 at 11:13 AM, ceberlin said:

Maybe this can be calculated from $config->timezone = 'Europe/Berlin' (or whatever), a setting which is there already in config?

Good point. If PHP is configured correctly in that regard, you should be able to use date() to set it dynamically.

$config->dbInitCommand = "SET NAMES '{charset}', time_zone = '" . date('P') . "' ";

I think there was a reason why I didn't try to convert the configured timezone to an offset, but I need to check that.

On 10/5/2019 at 11:13 AM, ceberlin said:

Uninstalling the Advanced Datetime field  leaves Advanced Datetime Inputfield and Datetime Extensions installed. Is this intended?

Definitely not intended. That should be taken care of by the Modules class. Did you get any warning messages while uninstalling the module?

Share this post


Link to post
Share on other sites
On 10/6/2019 at 2:58 PM, BitPoet said:

I think there was a reason why I didn't try to convert the configured timezone to an offset, but I need to check that.

Did you get any warning messages while uninstalling the module?

1. Your manual setting could serve well as a fallback in case there is a problem with the calculation?

2. No warning message, as far as I remember.

Share this post


Link to post
Share on other sites

Quick question: will this module work as intended if you turn off output formatting? I really like the sub-selector functionality like date.year=2019, but won't need the subfields like $page->date->year since I use Carbon for date fields in my frontend. Or are these two dependant on each other in any way?

Share this post


Link to post
Share on other sites
2 hours ago, d'Hinnisdaël said:

Quick question: will this module work as intended if you turn off output formatting? I really like the sub-selector functionality like date.year=2019, but won't need the subfields like $page->date->year since I use Carbon for date fields in my frontend. Or are these two dependant on each other in any way?

I'm not familiar enough with Carbon to make a guess how well DateTimeAdvanced works with it. Generally, every operation/function call that expects a string should still work the same as with a regular Datetime field when output formatting is off, since my helper object has a ___toString() method that returns the unix timestamp.

If you just want to add database subfield selectors to your system and don't want to search arrays or plug in your own subfield patterns, you can use this stripped-down module:

<?php

/**
 * ## Enhanced Datetime Field
 *
 * Allows searching for individual components of datetime fields in
 * standard subfield syntax in the database.
 *
 * Note that this doesn't work for searching in PageArrays. For that,
 * use the DatetimeAdvanced Fieldtype instead.
 *
 * ## Possible subfields
 *
 * - day
 * - month
 * - year (4-digit)
 * - hour (0..23)
 * - minutes
 * - seconds
 * - day_of_week (0..6, 0 = Sunday)
 * - day_of_year (0..365)
 * - week_of_year (1..53)
 * - date (just the date part, yyyy-mm-dd)
 * - time (just the time part, hh:mm:ss)
 *
 * ## Example
 *
 * ```
 * $pagelist = $pages->find("mydatefield.year=2016");
 * ```
**/

class FieldtypeDatetimePlus extends FieldtypeDatetime implements Module, ConfigurableModule {
	public static function getModuleInfo() {
		return array(
			"title"			=>	"Datetime Plus",
			"summary"		=>	wire()->_("Datetime field with extended search syntax. Needs timezone support enabled in MySQL, see http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html for details."),
			"version"		=>	"0.0.1"
		);
	}

	protected static $_operators = array(
		"day"			=>	array("d", "d"),
		"month"			=>	array("c", "m"),
		"year"			=>	array("Y", "Y"),
		"hour"			=>	array("H", "H"),
		"minutes"		=>	array("i", "i"),
		"seconds"		=>	array("s", "s"),
		"day_of_week"	=>	array("w", "w"),
		"day_of_year"	=>	array("j", "z"),
		"week_of_year"	=>	array("v", "W"),
		"date"			=>	array("%Y-%m-%d", "Y-m-d"),
		"time"			=>	array("T", "H:i:s"),
	);

	/**
	 * Match a date/time value in the database, as used by PageFinder
	 *
	 */
	public function getMatchQuery($query, $table, $subfield, $operator, $value) {
		
		if($subfield != "data") {
			if(! $this->isAllowedSubfield($subfield)) {
				throw new WireException(sprintf($this->_("Unknown subfield name for datetime field: %s"), $subfield));
			}
			if($subfield == "date" || $subfield == "time") {
				$value = preg_replace('/[^0-9:-]/', '', $value);
			} else {
				$value = $this->sanitizer->int($value);
			}
			if($operator == "day_of_year") {
				$value += 1;
			}
			$database = $this->wire("database");
			if($database->isOperator($operator)) {
				$table = $database->escapeTable($table);
				$value = $database->escapeStr($value);
				$formatarg = self::$_operators[$subfield][0];
				$formatarg = preg_replace('/([a-zA-Z])/', '%$1', $formatarg);
				$query->where("DATE_FORMAT({$table}.data, '{$formatarg}'){$operator}'$value'");
			}
		} else {
			$value = (int) $this->_sanitizeValue($value); 
			if($value) $value = date('Y-m-d H:i:s', $value); 
				else $value = '';
			$database = $this->wire('database');
			if($database->isOperator($operator)) {
				$table = $database->escapeTable($table);
				$subfield = $database->escapeCol($subfield);
				$value = $database->escapeStr($value);
				$query->where("$table.{$subfield}{$operator}'$value'");
			}
		}
		return $query; 
	}

	
	public function isAllowedSubfield($subfield) {
		return array_key_exists($subfield, self::$_operators);
	}

	public function getInputfield(Page $page, Field $field) {
		$inputfield = $this->modules->get('InputfieldDatetime'); 
		$inputfield->class = $this->className();
		return $inputfield; 
	}

	/**
	 * Return array with information about what properties and operators can be used with this field.
	 * 
	 * #pw-group-finding
	 * 
	 * @param Field $field
	 * @param array $data Array of extra data, when/if needed
	 * @return array See `FieldSelectorInfo` class for details.
	 *
	 */
	public function ___getSelectorInfo(Field $field, array $data = array()) {
		if($data) {}
		$selectorInfo = $this->wire(new FieldSelectorInfo());
		$info = $selectorInfo->getSelectorInfo($field);
		
		foreach(array_keys(self::$_operators) as $op) {
			$info["subfields"][$op] = array(
				"name"	=> $op,
				"label" => $op,
				"operators" => $info["operators"],
				"input" => "text",
				"hint" => "",
				"options" => array()
			);
		}
		
		return $info;
	}

	public function ___getModuleConfigInputfields() {
		
		$ifs = new InputfieldWrapper();
		
		$cmd = $this->config->dbInitCommand;
		$off = date('O');
		$needUpdate = false;
		if(strlen($off) > 0) {
			if(preg_match('/time_zone/i', $cmd)) {
				$needUpdate = false;
				return $ifs;
			}
			// PHP returns +0200, MySQL wants +02:00
			$off = preg_replace('/(\d\d)$/', ':$1', $off);
			// Append timezone set statement
			$cmd .= sprintf(", time_zone = '%s' ", $off);
		}
		
		$f = $this->modules->get("InputfieldMarkup");
		$f->label = $this->_("Enable timezone support in MySQL");
		$f->description = $this->_("For datetime plus database selectors to be reliable, PHP and MySQL need to add/subtract the same offsets for datetime and timestamp values.") .
			" " .
			$this->_("To accomplish that, the timezone setting in MySQL needs to be set to the same value as in PHP.") .
			" " .
			$this->_("Copy the following line to site/config.php:")
		;

		$f->attr(
			'value',
			"<code>" .
			"\$config->dbInitCommand = \"$cmd\";" .
			"</code>"
		);
		$f->notes = $this->_("Important: timezone data must be loaded in MySQL! See [this link](http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html) for details.");
		
		$ifs->append($f);
		
		return $ifs;
		
	}
	
}

 

  • Like 1
  • Thanks 1

Share this post


Link to post
Share on other sites

Thanks for taking the time and splitting this up ☃️ Much appreciated. I'll try this out with my next project where it'll come in really handy.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Gadgetto
      SnipWire - Snipcart integration for ProcessWire
      Snipcart is a powerful 3rd party, developer-first HTML/JavaScript shopping cart platform. SnipWire is the missing link between Snipcart and the content management framework ProcessWire.
      With SnipWire, you can quickly turn any ProcessWire site into a Snipcart online shop. The SnipWire plugin helps you to get your store up and running in no time. Detailed knowledge of the Snipcart system is not required.
      SnipWire is free and open source licensed under Mozilla Public License 2.0! A lot of work and effort has gone into development. It would be nice if you could donate an amount to support further development:

      Status update links (inside this thread) for SnipWire development
      2020-01-21 -- Snipcart v3 - when will the new cart system be implemented? 2020-01-19 -- integrated taxes provider finished (+ very flexible shipping taxes handling) 2020-01-14 -- new date range picker, discount editor, order notifiactions, order statuses, and more ... 2019-11-15 -- orders filter, order details, download + resend invoices, refunds 2019-10-18 -- list filters, REST API improvements, new docs platform, and more ... 2019-08-08 -- dashboard interface, currency selector, managing Orders, Customers and Products, Added a WireTabs, refinded caching behavior 2019-06-15 -- taxes provider, shop templates update, multiCURL implementation, and more ... 2019-06-02 -- FieldtypeSnipWireTaxSelector 2019-05-25 -- SnipWire will be free and open source Plugin Key Features
      Fast and simple store setup Full integration of the Snipcart dashboard into the ProcessWire backend (no need to leave the ProcessWire admin area) Browse and manage orders, customers, discounts, abandoned carts, and more Process refunds and send customer notifications from within the ProcessWire backend Process Abandoned Carts + sending messages to customers from within the ProcessWire backend Complete Snipcart webhooks integration (all events are hookable via ProcessWire hooks) Integrated taxes provider (which is more flexible then Snipcart own provider) Useful Links
      SnipWire in PW modules directory (alpha version only available via GitHub) SnipWire Docs (please note that the documentation is a work in progress) SnipWire @GitHub (feature requests and suggestions for improvement are welcome - I also accept pull requests) Snipcart Website  
      ---- INITIAL POST FROM 2019-05-25 ----
       
    • By d'Hinnisdaël
      Happy new year, everybody 🥬
      I've been sitting on this Dashboard module I made for a client and finally came around to cleaning it up and releasing it to the wider public. This is how it looks.
      ProcessWire Dashboard

      If anyone is interested in trying this out, please go ahead! I'd love to get some feedback on it. If this proves useful and survives some real-world testing, I'll add this to the module directory.
      Download
      You can find the latest release on Github.
      Documentation
      Check out the documentation to get started. This is where you'll find information about included panel types and configuration options.
      Custom Panels
      My goal was to make it really simple to create custom panels. The easiest way to do that is to use the panel type template and have it render a file in your templates folder. This might be enough for 80% of all use cases. For anything more complex (FormBuilder submissions? Comments? Live chat?), you can add new panel types by creating modules that extend the DashboardPanel base class. Check out the documentation on custom panels or take a look at the HelloWorld panel to get started. I'm happy to merge any user-created modules into the main repo if they might be useful to more than a few people.
       Disclaimer
      This is a pre-release version. Please treat it as such — don't install it on production sites. Just making sure 🍇
      Roadmap
      These are the things I'm looking to implement myself at some point. The wishlist is a lot longer, but those are the 80/20 items that I probably won't regret spending time on.
      Improve documentation & add examples ⚙️ Panel types Google Analytics ⚙️ Add new page  🔥 Drafts 🔥 At a glance / Page counter 404s  Layout options Render multiple tabs per panel panel groups with heading and spacing between ✅ panel wrappers as grid item (e.g. stacked notices) ✅ Admin themes support AdminThemeReno and AdminThemeDefault ✅ Shortcuts panel add a table layout with icon, title & summary ✅ Chart panel add default styles for common chart types ✅ load chart data from JS file (currently passed as PHP array) Collection panel support image columns ✅ add buttons: view all & add new ✅
    • By Robin S
      This module is inspired by and similar to the Template Stubs module. The author of that module has not been active in the PW community for several years now and parts of the code for that module didn't make sense to me, so I decided to create my own module. Auto Template Stubs has only been tested with PhpStorm because that is the IDE that I use.
      Auto Template Stubs
      Automatically creates stub files for templates when fields or fieldgroups are saved.
      Stub files are useful if you are using an IDE (e.g. PhpStorm) that provides code assistance - the stub files let the IDE know what fields exist in each template and what data type each field returns. Depending on your IDE's features you get benefits such as code completion for field names as you type, type inference, inspection, documentation, etc.
      Installation
      Install the Auto Template Stubs module.
      Configuration
      You can change the class name prefix setting in the module config if you like. It's good to use a class name prefix because it reduces the chance that the class name will clash with an existing class name.
      The directory path used to store the stub files is configurable.
      There is a checkbox to manually trigger the regeneration of all stub files if needed.
      Usage
      Add a line near the top of each of your template files to tell your IDE what stub class name to associate with the $page variable within the template file. For example, with the default class name prefix you would add the following line at the top of the home.php template file:
      /** @var tpl_home $page */ Now enjoy code completion, etc, in your IDE.

      Adding data types for non-core Fieldtype modules
      The module includes the data types returned by all the core Fieldtype modules. If you want to add data types returned by one or more non-core Fieldtype modules then you can hook the AutoTemplateStubs::getReturnTypes() method. For example, in /site/ready.php:
      // Add data types for some non-core Fieldtype modules $wire->addHookAfter('AutoTemplateStubs::getReturnTypes', function(HookEvent $event) { $extra_types = [ 'FieldtypeDecimal' => 'string', 'FieldtypeLeafletMapMarker' => 'LeafletMapMarker', 'FieldtypeRepeaterMatrix' => 'RepeaterMatrixPageArray', 'FieldtypeTable' => 'TableRows', ]; $event->return = $event->return + $extra_types; }); Credits
      Inspired by and much credit to the Template Stubs module by mindplay.dk.
       
      https://github.com/Toutouwai/AutoTemplateStubs
      https://modules.processwire.com/modules/auto-template-stubs/
    • By Mike Rockett
      Jumplinks for ProcessWire
      Release: 1.5.60
      Composer: rockett/jumplinks
      ⚠️ NOTICE: 1.5.60 is an important security patch-release for an XSS vulnerability discovered by @phlp. It's HIGHLY RECOMMENDED that all Jumplinks users update to the latest version as soon as possible.
      Jumplinks is an enhanced version of the original ProcessRedirects by Antti Peisa.
      The Process module manages your permanent and temporary redirects (we'll call these "jumplinks" from now on, unless in reference to redirects from another module), useful for when you're migrating over to ProcessWire from another system/platform. Each jumplink supports wildcards, shortening the time needed to create them.
      Unlike similar modules for other platforms, wildcards in Jumplinks are much easier to work with, as Regular Expressions are not fully exposed. Instead, parameters wrapped in curly braces are used - these are described in the documentation.
      Under Development: 2.0, to be powered by FastRoute
      As of version 1.5.0, Jumplinks requires at least ProcessWire 2.6.1 to run.
      View on GitLab
      Download via the Modules Directory
      Read the docs
      Features
      The most prominent features include:
      Basic jumplinks (from one fixed route to another) Parameter-based wildcards with "Smart" equivalents Mapping Collections (for converting ID-based routes to their named-equivalents without the need to create multiple jumplinks) Destination Selectors (for finding and redirecting to pages containing legacy location information) Timed Activation (activate and/or deactivate jumplinks at specific times) 404-Monitor (for creating jumplinks based on 404 hits) Additionally, the following features may come in handy:
      Stale jumplink management Legacy domain support for slow migrations An importer (from CSV or ProcessRedirects) Feedback & Feature Requests
      I’d love to know what you think of this module. Please provide some feedback on the module as a whole, or even regarding smaller things that make it whole. Also, please feel free to submit feature requests and their use-cases.
      Note: Features requested so far have been added to the to-do list, and will be added to 2.0, and not the current dev/master branches.
      Open Source

      Jumplinks is an open-source project, and is free to use. In fact, Jumplinks will always be open-source, and will always remain free to use. Forever. If you would like to support the development of Jumplinks, please consider making a small donation via PayPal.
      Enjoy! 🙂
    • By Robin S
      Add Image URLs
      Allows images/files to be added to Image/File fields by pasting URLs.

      Usage
      Install the Add Image URLs module.
      A "Paste URLs" button will be added to all image and file fields. Use the button to show a textarea where URLs may be pasted, one per line. Images/files are added when the page is saved.
       
      https://github.com/Toutouwai/AddImageUrls
      https://modules.processwire.com/modules/add-image-urls/
×
×
  • Create New...