Jump to content

FieldtypeDatetimeAdvanced - subfield selectors for date/time


BitPoet
 Share

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
Link to comment
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
Link to comment
Share on other sites

  • 2 years later...

@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
Link to comment
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
Link to comment
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.

Link to comment
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

Link to comment
Share on other sites

  • 2 weeks later...

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
Link to comment
Share on other sites

  • 3 weeks later...
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
Link to comment
Share on other sites

  • 3 months later...

@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

Link to comment
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.

 

Link to comment
Share on other sites

  • 6 months later...

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?

Link to comment
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?

Link to comment
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.

Link to comment
Share on other sites

  • 3 months later...

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?

Link to comment
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
Link to comment
Share on other sites

  • 5 months later...

First, thanks for your effort making this extension!

Environment: DatetimeAdvanced v1.0.4, PW 3.0.148, PHP 7.4.6, mysql 5.7., Timezone ist set.

While accessing subfields as selectors within a $pages-find() works fine, i am having trouble using subfields within PageArray results. Definitely i miss something here. Maybe i should stop working for today, but meanwhile someone maybe have a hint what i am doing wrong...

Thx, Olaf

<?php 

foreach($results as $result) {

	// Directly access gives me "Notice: Trying to get property 'year' of non-object"
	echo $result->programm_date_advanced->year;
	
	// strftime shorthand gives me "Fatal error: Uncaught Error: Call to a member function strftime() on string"
	echo $result->programm_date_advanced->strftime('%Y');

	// Using strftime plain gives me "Notice: A non well formed numeric value encountered"
	echo strftime('%Y', $result->programm_date_advanced);

	// Using strftime unformatted gives me "Warning: strftime() expects parameter 2 to be int, object given"
	echo strftime('%Y', $result->getUnformatted('programm_date_advanced'));

	// Plain output is o.k.
	echo $result->programm_date_advanced; // e.g. 10.07.2020

}
?>

 

Link to comment
Share on other sites

17 hours ago, olafgleba said:

While accessing subfields as selectors within a $pages-find() works fine, i am having trouble using subfields within PageArray results. Definitely i miss something here. Maybe i should stop working for today, but meanwhile someone maybe have a hint what i am doing wrong...

I'm not really familiar with this module, but it looks like the formatted output will indeed be a single date. Have you tried $result->getUnformatted('programm_date_advanced') to get the unformatted version?

Link to comment
Share on other sites

1 hour ago, teppo said:

Have you tried $result->getUnformatted('programm_date_advanced') to get the unformatted version?

That gives me the expected result (just like the output of a field with the default `datetime` Fieldtype).

echo $return->getUnformatted('programm_date_advanced') // e.g. `1562233140`

 

Link to comment
Share on other sites

  • 5 weeks later...

Hi,

This module does not appear to be compatible with the latest master. Seems the refactoring of InputfieldDatetime since 3.0.148 is the culprit.

Failed to construct module: \InputfieldDatetimeAdvanced - Method InputfieldDatetimeAdvanced::getInputFormats does not exist or is not callable in this context

I added getInputFormats() from a previous version of InputfieldDatetime and that appears to have fixed it:

<?php

	/**
	 * Get the input format string for the user's language
	 *
	 * thanks to @oliverwehn (#1463)
	 *
	 * @param bool $getString Specify true to get a format string rather than an array
	 * @return array|string of dateInputFormat timeInputFormat
	 *
	 */
	protected function getInputFormats($getString = false) {

		$inputFormats = array();
		$language = $this->wire('user')->language;
		$useLanguages = $this->wire('languages') && $language && !$language->isDefault();

		foreach(array('date', 'time') as $type) {

			$inputFormat = '';

			if($useLanguages) {
				$inputFormat = trim($this->getSetting("{$type}InputFormat{$language->id}"));
			}

			if(!strlen($inputFormat)) {
				// fallback to default language
				$inputFormat = $this->get("{$type}InputFormat");
			}

			$inputFormats[] = $inputFormat;
		}

		if($getString) return trim(implode(' ', $inputFormats));

		return $inputFormats;
	}

Cheers,

Chris

  • Like 1
Link to comment
Share on other sites

  • 1 month later...

Am I right to say that your example

$start = date('z');
$end = $start + 7;
$sevendays = $pages->find("mydatefield.day_of_year>=$start, mydatefield.day_of_year<$end");

doesn't work, if $start is within the last week of December? It won't find pages for January, since day_of_year starts with 1 again.

My SQL solution for this would be something like

select * from my_table where (DAYOFYEAR(my_date)+366 - DAYOFYEAR(CURDATE())) % 366 < 7

Is there an equivalent PW query solution for this?

Thanks, Florian

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Similar Content

    • By MarkE
      This fieldtype and inputfield bundle was built for storing measurement values within a field, rendering them in a variety of formats and converting them to other units or otherwise modifying them via the API.
      The API consists of a number of predefined functions, some of which include...
      render() for rendering the measurement object, valueAs() for converting the value to another unit value, convertTo() for converting the whole measurement object to different units, and add() and subtract() for for modifying the stored value by the value (converted as required) in another measurement. In the admin the inputfield includes a checkbox (which can be optionally disabled) for converting values on page save. For an example if a value was typed in as centimeters, the unit was changed to metres, and the page saved with this checkbox selected, said value would be automatically converted so that e.g. 170 cm becomes 1.7 m.

      A simple length field using Fieldtype Measurement and Inputfield Measurement.
      Combination units (e.g. feet and inches) are also supported.
      Please note that this module is 'proof of concept' at the moment - there are limited units available and quite a lot of code tidying to do. More units will be added shortly.
      See the GitHub at https://github.com/MetaTunes/FieldtypeMeasurement for full details and updates.
    • By tcnet
      File Manager for ProcessWire is a module to manager files and folders from the CMS backend. It supports creating, deleting, renaming, packing, unpacking, uploading, downloading and editing of files and folders. The integrated code editor ACE supports highlighting of all common programming languages.
      https://github.com/techcnet/ProcessFileManager

      Warning
      This module is probably the most powerful module. You might destroy your processwire installation if you don't exactly know what you doing. Be careful and use it at your own risk!
      ACE code editor
      This module uses ACE code editor available from: https://github.com/ajaxorg/ace

      Dragscroll
      This module uses the JavaScript dragscroll available from: http://github.com/asvd/dragscroll. Dragscroll adds the ability to drag the table horizontally with the mouse pointer.
      PHP File Manager
      This module uses a modified version of PHP File Manager available from: https://github.com/alexantr/filemanager
       
    • By tcnet
      This module implements the website live chat service from tawk.to. Actually the module doesn't have to do much. It just need to inserted a few lines of JavaScript just before the closing body tag </body> on each side. However, the module offers additional options to display the widget only on certain pages.
      Create an account
      Visit https://www.tawk.to and create an account. It's free! At some point you will reach a page where you can copy the required JavaScript-code.

      Open the module settings and paste the JavaScript-code into the field as shown below. Click "Submit" and that's all.

      Open the module settings
      The settings for this module are located int the menu Modules=>Configure=>LiveChatTawkTo.

       
    • By tcnet
      Session Viewer is a module for ProcessWire to list session files and display session data. This module is helpful to display the session data of a specific session or to kick out a logged in user by simply delete his session file. After installation the module is available in the Setup menu.

      The following conditions must be met for the module to work properly:
      Session files
      Session data must be stored in session files, which is the default way in ProcessWire. Sessions stored in the database are not supported by this module. The path to the directory where the session files are stored must be declared in the ProcessWire configuration which is by default: site/assets/sessions.
      Serialize handler
      In order to transform session data easier back to a PHP array, the session data is stored serialized. PHP offers a way to declare a custom serialize handler. This module supports only the default serialize handlers: php, php_binary and php_serialize. WDDX was dropped in PHP 7.4.0 and is therefore not supported by this module as well as any other custom serialize handler. Which serialize handler is actually used you can find out in the module configuration which is available under Modules=>Configure=>SessionViewer.

      Session data
      The session data can be displayed in two different ways. PHP's default output for arrays print_r() or by default for this module nice_r() offered on github: https://github.com/uuf6429/nice_r. There is a setting in the module configuration if someone prefers print_r(). Apart from the better handling and overview of the folded session data the output of nice_r() looks indeed nicer.

      Links
      ProcessWire module directory
      github.com
    • By Robin S
      Repeater Easy Sort
      Adds a compact "easy-sort" mode to Repeater and Repeater Matrix, making those fields easier to sort when there are a large number of items.
      The module also enhances Repeater Matrix by allowing a colour to be set for each matrix type. This colour is used in the item headers and in the "add new" links, to help visually distinguish different matrix types in the inputfield.
      Screencasts
      A Repeater field

      A Repeater Matrix field with custom header colours

      Easy-sort mode
      Each Repeater/Matrix item gets an double-arrow icon in the item header. Click this icon to enter easy-sort mode.
      While in easy-sort mode:
      The items will reduce in width so that more items can be shown on the screen at once. The minimum width is configurable in the field settings. Any items that were in an open state are collapsed, but when you exit easy-sort mode the previously open items will be reopened. You can drag an item left/right/up/down to sort it within the items. The item that you clicked the icon for is shown with a black background. This makes it easier to find the item you want to move in easy-sort mode. You can click an item header to open the item. An "Exit easy-sort mode" button appears at the bottom of the inputfield. Configuration
      In the field settings for Repeater and Repeater Matrix fields you can define a minimum width in pixels for items in easy-sort mode. While in easy-sort mode the items will be sized to neatly fill the available width on any screen size but will never be narrower than the width you set here.
      In the field settings for Repeater Matrix you can define a custom header colour for each matrix type using an HTML "color" type input. The default colour for this type of input is black, so when black is selected in the input it means that no custom colour will be applied to the header.
      Exclusions
      The easy-sort mode is only possible on Repeater/Matrix fields that do not use the "item depth" option.
       
      https://github.com/Toutouwai/RepeaterEasySort
      https://processwire.com/modules/repeater-easy-sort/
×
×
  • Create New...