Date issue V5.98 - won't accept date over 50 years ago?

Please report bugs and any annoyances here. Kindly include all possible details: steps to reproduce, expected result, actual result, screenshots, ... etc.
Post Reply
peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Date issue V5.98 - won't accept date over 50 years ago?

Post by peebee » 2021-11-10 03:09

Unexplainable issue in V5.98 with date fields? Unable to record any dates prior to 1970??

To recreate:
(1) Create a date field in AppGini (make it required or not - makes no difference)
(2) Generate your app and try to select a date prior to 1969 (either via date-picker or dropdown)

Result:
If a required field - Error: DateField is required
If not a required field - records blank date

Thought it might have been some edit I'd made so I created a vanilla app with just 1 x table, 3 fields and zero edits to test. Result was same as above. Appears to be an AppGini issue. Tried to test on AppGini's Northwind demo but I see it's still V5.97 (which doesn't have this issue).

I presume it has to do with this in the V5.98 changelog:
Support for configuring max and min years allowed in date fields via hooks: In hooks/tablename.php or hooks/__global.php, add this in the global scope (outside any function definitions):
// Change tablename and fieldname to actual table and field names
define('tablename.fieldname.MinYear', 2010);
define('tablename.fieldname.MaxYear', 2030);

// Dynamic dates also works:
define('tablename.fieldname.MinYear', date('Y') - 1); // = last year
define('tablename.fieldname.MaxYear', date('Y') + 2); // = 2 years ahead
I tried adding the relevant define hook mentioned above. Didn't work.

Currently looking at generated DateCombo.php and date_combo.class.php to see if I can find the cause.

A solution would be very nice as this particular project relies on historical dates and that's proving a problem!

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by peebee » 2021-11-10 03:30

I tried adding the relevant define hook mentioned above. Didn't work.
I should add that including the following in the hooks file:

Code: Select all

define('tablename.fieldname.MinYear', 2010);
define('tablename.fieldname.MaxYear', 2030);
did actually change the behaviour of both the date-picker and the dropdowns (in that it restricted max and min dates to select from) however it made no difference to the actual problem if you set the MinYear to any date over 50 years ago. You can select an earlier date - but it won't save it.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by peebee » 2021-11-10 04:54

Okay, delving deeper. The problem is when trying to save any date prior to 01 January 1970 which is of course the Unix Epoch and also the default date for most date pickers.

I thought it may have something to do with the date format being stored but as we can now change that in the AppGini Admin are, I tried some other combinations. Didn't seem to make a difference.

I also see that AppGini V5.98 and completely dispensed with some generated files from past versions (including date_combo.class.php).

I should also clarify the way to recreate the problem from my initial post, which is not so clear.

To recreate:
(1) Create a date field in AppGini (make it required or not - makes no difference)
(2) Generate your app.
(3) Select a date prior to 01 Jan 1970 (either via date-picker or dropdown)
(4) Save

Result:
If a required field - Error: DateField "you cannot leave this field empty"
If not a required field - records blank (NULL) date

Default date of 01 Jan 1970 is the key I think. Just need to figure out why???

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Date issue V5.98 - won't accept date prior to Jan 01 1970?

Post by peebee » 2021-11-15 03:13

Feels a lot like I'm talking to myself here :-) Is anybody else experiencing this date issue with V5.98 or is it just me???

I notice there are huge structural changes between 5.98 and previous versions. Particularly the new definitions.php file and associated functions. I'm sure those changes are leading to the problem (for me at least) but I just can't work out where?

Here is what's happening for me: https://www.loom.com/share/d87f1ebb2c90 ... cb19062891

Try to save any date prior to January 01 1970 - NULL stored in database.

An interesting thing I've noticed while trying to debug this problem for myself;

I am in Australia. Time zone is Australia/Sydney (currently GMT+11 as we have Daylight Savings - normally +10)

If "Time Zone" in Appgini is left at the default of America/New_York, the default date of "today" if set is out for me by one day prior as the US is one day behind Australia. Understandable.

With America/New_York set as time zone, I can save any date from and including 01 January 1970 but nothing prior to that date.

With Australia/Sydney set as time zone in Appgini, "today" is actually today for me. With Aus time zone though, I can only save any date from and including 02 January 1970 but nothing prior. I can't save 01 January 1970.

The Unix Epoch/default date picker date of Jan 01 1970 is obviously where the issue originates. Looks like either a strtotime() somewhere is not converting the selected pre 1970 dates to a correct Unix timestamp format or the date-picker itself is failing in that regard? I suspect it's the date-picker function. I'm currently trawling through code trying to figure out what might be wrong???

I have tried all different date formats and date separators (including hyphen which should not fail) - make no difference. Experimented with the data type in phpmyadmin. No difference, all pre 1970 dates stored as NULL.

Any guidance appreciated and also would love to know if any other user of V5.98 is experiencing the same issue or is it just me?

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by peebee » 2021-11-15 05:00

OK, getting somewhere now. Only taken me a few days of trial and error.....

I can resolve the problem and save ALL dates by replacing this entire code block in incCommon.php (which is new in Version 5.98)

Code: Select all

        #########################################################

	function validMySQLDate($date) {
		$date = trim($date);
		$parts = explode('-', $date);
		return (
			count($parts) == 3
			// see https://dev.mysql.com/doc/refman/8.0/en/datetime.html
			&& intval($parts[0]) >= 1000
			&& intval($parts[0]) <= 9999
			&& intval($parts[1]) >= 1
			&& intval($parts[1]) <= 12
			&& intval($parts[2]) >= 1
			&& intval($parts[2]) <= 31
			&& strtotime($date) > 0
		);
	}

	#########################################################

	function parseMySQLDate($date, $altDate) {
		// is $date valid?
		if(validMySQLDate($date)) return trim($date);

		if($date != '--' && validMySQLDate($altDate)) return trim($altDate);

		if($date != '--' && $altDate && is_numeric($altDate))
			return @date('Y-m-d', @time() + ($altDate >= 1 ? $altDate - 1 : $altDate) * 86400);

		return '';
	}

	#########################################################
with this corresponding code taken directly from incCommon.php (Version 5.97 and prior):

Code: Select all

        #########################################################

	function parseMySQLDate($date, $altDate) {
		// is $date valid?
		if(preg_match("/^\d{4}-\d{1,2}-\d{1,2}$/", trim($date))) {
			return trim($date);
		}

		if($date != '--' && preg_match("/^\d{4}-\d{1,2}-\d{1,2}$/", trim($altDate))) {
			return trim($altDate);
		}

		if($date != '--' && $altDate && intval($altDate)==$altDate) {
			return @date('Y-m-d', @time() + ($altDate >= 1 ? $altDate - 1 : $altDate) * 86400);
		}

		return '';
	}

	#########################################################
New code is PHP 8.x compatability I presume? Whilst I can get dates working again with this edit, I don't want to ruin anything else.

Ahmed or anybody else - any thoughts/fix?

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by a.gneady » 2021-11-17 16:16

Thanks for taking the time to inspect this issue. I can confirm I'm able to reproduce it, so I'll inspect this issue and make a fix for it in the next release.
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by peebee » 2021-11-18 04:36

Thanks for the confirmation of the bug Ahmed.

Will the patch I mentioned above (replace V5.98 code block in incCommon.php with corresponding code block from V5.97) affect anything else? Is that a safe and effective interim fix for the problem? V5.98 is not practical to use at this moment with this bug.

Is a new bug fix version imminent? Any estimated time frame? Thanks.

SkayyHH
Veteran Member
Posts: 425
Joined: 2015-04-27 21:18

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by SkayyHH » 2021-11-19 15:50

I would like to know that, too. The bug is serious, e.g. for birth dates.

Is the work around above ok?

User avatar
a.gneady
Site Admin
Posts: 1281
Joined: 2012-09-27 14:46
Contact:

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by a.gneady » 2021-11-20 18:09

To be on the safe side, I'd recommend not removing the function validMySQLDate() .. So, it's safe to revert parseMySQLDate() to the one of version 5.97, but I don't recommend removing validMySQLDate().
Is a new bug fix version imminent? Any estimated time frame? Thanks.
Well, yes possibly. I'll work on this in the upcoming weeks. No ETA yet I'm afraid :roll:
:idea: AppGini plugins to add more power to your apps:
  • DataTalk is an innovative AppGini plugin based on ChatGPT that allows you to interact with your AppGini database using natural language questions, without writing any SQL. Check the demo video
  • Mass Update plugin: Update multiple records at once and improve your workflow efficiency.
  • Check our other plugins and get a generous discount of up to 30% when buying 2 or more plugins.

hubert
Veteran Member
Posts: 50
Joined: 2018-12-06 22:32

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by hubert » 2022-02-19 20:33

Well have the same issue and noted it appeared also with 5.98.
Happy to have a fix soon, is 22.11 solving ?
Edit : sadly seems not as I installed the trial version and still the same issue ...
Really have hard need of using those dates.

Thx for work !

hubert
Veteran Member
Posts: 50
Joined: 2018-12-06 22:32

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by hubert » 2022-02-19 21:00

For now, Idid degrade from 5.98 to 5.97 which is ok with this ...
And will wait to renew the version till issue's ok

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by peebee » 2022-02-21 05:59

This date issue appears to have been fixed and as far as I can see is not present in V22.11? Historical dates are working correctly for me?

Have you tried a complete fresh install of newly generated V22.11 files?

You should notice a difference between generated inCommon.php in V5.98 (where dates weren't working) and V22.11 (dates are working) in this
function validMySQLDate($date) {

New working function looks like this:

Code: Select all

function validMySQLDate($date) {
		$date = trim($date);

		try {
			$dtObj = new DateTime($date);
		} catch(Exception $e) {
			return false;
		}

		$parts = explode('-', $date);
		return (
			count($parts) == 3
			// see https://dev.mysql.com/doc/refman/8.0/en/datetime.html
			&& intval($parts[0]) >= 1000
			&& intval($parts[0]) <= 9999
			&& intval($parts[1]) >= 1
			&& intval($parts[1]) <= 12
			&& intval($parts[2]) >= 1
			&& intval($parts[2]) <= 31
		);
	}

hubert
Veteran Member
Posts: 50
Joined: 2018-12-06 22:32

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by hubert » 2022-02-21 07:50

Oh, is it ?
I tried on trial 22.11, had the same issue than in 5.98 ...

But as strange as it seems, tried again and .. working !
Ahmed, could you confirm you made a change or is it something like a monkey bug, once it's here, once not ?

hubert
Veteran Member
Posts: 50
Joined: 2018-12-06 22:32

Re: Date issue V5.98 - won't accept date over 50 years ago?

Post by hubert » 2022-02-22 20:27

fresh and clean install of 22.11 (thx for twosday offer), no more issue with dates less than 1970.
Bug seems to be resolved, thx dev team !

Post Reply