Default date 01/01/1970 when a calculated field is null

Please report bugs and any annoyances here. Kindly include all possible details: steps to reproduce, expected result, actual result, screenshots, ... etc.
Post Reply
balfons
Veteran Member
Posts: 56
Joined: 2018-10-22 15:27

Default date 01/01/1970 when a calculated field is null

Post by balfons » 2021-04-06 15:23

Hi everybody!

I have several date calculated fields and when there is no date to show, AppGini prints 01/01/1970. I have made a workaround in one field with a SQL query that puts a forecasted date 3 months later, but it only wprks for this field.

So, I am finding a solution for this fields. Any suggestion?

Thanks in advance!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1070
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Default date 01/01/1970 when a calculated field is null

Post by jsetzer » 2021-04-06 16:31

Is it date, datetine or timestamp?
Kind regards,
<js />

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1070
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Default date 01/01/1970 when a calculated field is null

Post by jsetzer » 2021-04-06 16:39

Additionally: can you check if all database migrations have run (update fields in admin area)

and

is the field nullable, does it have a default value

and

can you post the SQL command of your calculation query
Kind regards,
<js />

balfons
Veteran Member
Posts: 56
Joined: 2018-10-22 15:27

Re: Default date 01/01/1970 when a calculated field is null

Post by balfons » 2021-04-07 11:34

Hi Jan!

it is a date field, read only (because is a calculated field) and doesn't have a default value. I have also checked that all database migrations have run.

Here is my code:

SELECT date_format(`table2`.`execution_date`,'%d/%m/%Y') FROM `table2`
LEFT JOIN `table1` ON `table2`.`corrective_action`=`table1`.`id`
WHERE `table1`.`id`='%ID%' AND `table2`.`status` = '2'
ORDER BY `table2`.`execution_date` DESC LIMIT 1

table1 is parent of table2. I want to show in table1 the date of the last record of table2 having a certain status. It works when I have some records that fulfil the condition but when I have no records, 01/01/1970 is shown.

thanks for helping!

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1070
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: Default date 01/01/1970 when a calculated field is null

Post by jsetzer » 2021-04-07 12:08

That's strange because MySQL's number_format function should return NULL on null or empty input but not "1970/01/01" (AKA: UNIX Epoch).

I've checked it in Adminer:

Code: Select all

select date_format('2021-04-07', '%d/%m/%Y');
select date_format('', '%d/%m/%Y');
select date_format(null, '%d/%m/%Y')
Results

Code: Select all

select date_format('2021-04-07', '%d/%m/%Y')
07/04/2021


select date_format('', '%d/%m/%Y')
NULL


select date_format(null, '%d/%m/%Y')
NULL
I've checked with 0:

Code: Select all

select date_format(0, '%d/%m/%Y');
// result in date-column: 0000-00-00
I've checked with unconvertibles:

Code: Select all

select date_format('aaa', '%d/%m/%Y');
// result in date-column: null
None of my tests returned UNIX Epoch from MySQL Query. This means for me that even if there is no value due to inner join date_format should return something different than UNIX Epoch.

So, the problem may be NOT on MySQL side but on PHP side. I know that, when calling PHP's date() function with an empty or invalid input parameter, it returns UNIX Epoch unless you convert the input using strtotime before.

I am wondering if this is just a display problem or a database problem. Can you please check your database table if whether 1970/01/01 has been stored or null or 0000-00-00.
Kind regards,
<js />

balfons
Veteran Member
Posts: 56
Joined: 2018-10-22 15:27

Re: Default date 01/01/1970 when a calculated field is null

Post by balfons » 2021-04-07 13:50

Hi Jan!

I have checked database and the value stored is null so maybe is a display problem

Post Reply