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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By MoritzLost
      This is a new module that provides a simple solution to clearing all your cache layers at once, and an extensible interface to perform various cache-related actions.
      The simple motivation behind this module was that I was tired of manually clearing caches in several places after deploying a change on a live site. The basic purpose of this module is a simple Clear all caches link in the Setup menu which clears out all caches, no matter where they hide. You can customize what exactly the module does through it's configuration menu:
      Expire or delete all cache entries in the database, or selectively clear caches by namespace ($cache API) Clear the the template render cache. Clear out specific folders inside your site's cache directory (/site/assets/cache) Refresh version strings for static assets to bust client-side browser caches (this requires some setup, see the full documentation for details). This is the basic function of the module. However, you can also add different cache management action through the API and execute them through the module's interface. For this advanced usage, the module provides:
      An interface to see all available cache actions and execute them. A system log and logging output on the module page to see verify what the module is doing. A CacheControlTools class with utility functions to clear out different caches. An API to add cache actions, execute them programmatically and even modify the default action. Permission management, allowing you granular control over which user roles can execute which actions. The complete documentation can be found in the module's README.
      Beta release
      Note that I consider this a Beta release. Since the module is relatively aggressive in deleting some caches, I would advise you to install in on a test environment before using it on a live site.
      Let me know if you're getting any errors, have trouble using the module or if you have suggestions for improvement!
      In particular, can someone let me know if this module causes any problems with the ProCache module? I don't own or use it, so I can't check. As far as I can tell, ProCache uses a folder inside the cache directory to cache static pages, so my module should be able to clear the ProCache site cache as well, I'd appreciate it if someone can test that for me.
      Future plans
      If there is some interest in this, I plan to expand this to a more general cache management solution. I particular, I would like to add additional cache actions. Some ideas that came to mind:
      Warming up the template render cache for publicly accessible pages. Removing all active user sessions. Let me know if you have more suggestions!
      Links
      https://github.com/MoritzLost/ProcessCacheControl ProcessCacheControl in the Module directory

    • By joshua
      This module is (yet another) way for implementing a cookie management solution.
      Of course there are several other possibilities:
      - https://processwire.com/talk/topic/22920-klaro-cookie-consent-manager/
      - https://github.com/webmanufaktur/CookieManagementBanner
      - https://github.com/johannesdachsel/cookiemonster
      - https://www.oiljs.org/
      - ... and so on ...
      In this module you can configure which kind of cookie categories you want to manage:

      You can also enable the support for respecting the Do-Not-Track (DNT) header to don't annoy users, who already decided for all their browsing experience.
      Currently there are four possible cookie groups:
      - Necessary (always enabled)
      - Statistics
      - Marketing
      - External Media
      All groups can be renamed, so feel free to use other cookie group names. I just haven't found a way to implement a "repeater like" field as configurable module field ...
      When you want to load specific scripts ( like Google Analytics, Google Maps, ...) only after the user's content to this specific category of cookies, just use the following script syntax:
      <script type="optin" data-type="text/javascript" data-category="statistics" data-src="/path/to/your/statistic/script.js"></script> <script type="optin" data-type="text/javascript" data-category="marketing" data-src="/path/to/your/mareketing/script.js"></script> <script type="optin" data-type="text/javascript" data-category="external_media" data-src="/path/to/your/external-media/script.js"></script> <script type="optin" data-type="text/javascript" data-category="marketing">console.log("Inline scripts are also working!");</script> The type has to be "optin" to get recognized by PrivacyWire, the data-attributes are giving hints, how the script shall be loaded, if the data-category is within the cookie consents of the user. These scripts are loaded asynchronously after the user made the decision.
      If you want to give the users the possibility to change their consent, you can use the following Textformatter:
      [[privacywire-choose-cookies]] It's planned to add also other Textformatters to opt-out of specific cookie groups or delete the whole consent cookie.
      You can also add a custom link to output the banner again with a link / button with following class:
      <a href="#" class="privacywire-show-options">Show Cookie Options</a> <button class="privacywire-show-options">Show Cookie Options</button> This module is still in development, but we already use it on several production websites.
      You find it here: https://github.com/blaueQuelle/privacywire/tree/master
      Download: https://github.com/blaueQuelle/privacywire/archive/master.zip
      I would love to hear your feedback 🙂
      Edit: Updated URLs to master tree of git repo
       
    • By David Karich
      Admin Page Tree Multiple Sorting
      ClassName: ProcessPageListMultipleSorting
      Extend the ordinary sort of children of a template in the admin page tree with multiple properties. For each template, you can define your own rule. Write each template (template-name) in a row, followed by a colon and then the additional field names for sorting.
      Example: All children of the template "blog" to be sorted in descending order according to the date of creation, then descending by modification date, and then by title. Type:
      blog: -created, -modified, title  Installation
      Copy the files for this module to /site/modules/ProcessPageListMultipleSorting/ In admin: Modules > Check for new modules. Install Module "Admin Page Tree Multible Sorting". Alternative in ProcessWire 2.4+
      Login to ProcessWire backend and go to Modules Click tab "New" and enter Module Class Name: "ProcessPageListMultipleSorting" Click "Download and Install"   Compatibility   I have currently tested the module only under PW 2.6+, but think that it works on older versions too. Maybe someone can give a feedback.     Download   PW-Repo: http://modules.processwire.com/modules/process-page-list-multiple-sorting/ GitHub: https://github.com/FlipZoomMedia/Processwire-ProcessPageListMultipleSorting     I hope someone can use the module. Have fun and best regards, David
    • By dimitrios
      Hello,
      this module can publish content of a Processwire page on a Facebook page, triggered by saving the Processwire page.
      To set it up, configure the module with a Facebook app ID, secret and a Page ID. Following is additional configuration on Facebook for developers:
      Minimum Required Facebook App configuration:
      on Settings -> Basics, provide the App Domains, provide the Site URL, on Settings -> Advanced, set the API version (has been tested up to v3.3), add Product: Facebook Login, on Facebook Login -> Settings, set Client OAuth Login: Yes, set Web OAuth Login: Yes, set Enforce HTTPS: Yes, add "https://www.example.com/processwire/page/" to field Valid OAuth Redirect URIs. This module is configurable as follows:
      Templates: posts can take place only for pages with the defined templates. On/Off switch: specify a checkbox field that will not allow the post if checked. Specify a message and/or an image for the post.
      Usage
      edit the desired PW page and save; it will post right after the initial Facebook log in and permission granting. After that, an access token is kept.
       
      Download
      PW module directory: http://modules.processwire.com/modules/auto-fb-post/ Github: https://github.com/kastrind/AutoFbPost   Note: Facebook SDK for PHP is utilized.


×
×
  • Create New...