Recent

Author Topic: LInux / MySQL date format  (Read 615 times)

OH1KH

  • Jr. Member
  • **
  • Posts: 59
LInux / MySQL date format
« on: November 06, 2022, 11:29:08 am »
Hi!

Just wondering have anyone seen problem(s) with dates fetch from MySQL database?

Code: Text  [Select][+][-]
  1. +----------------+---------------+------+-----+---------+----------------+
  2. | Field          | Type          | Null | Key | Default | Extra          |
  3. +----------------+---------------+------+-----+---------+----------------+
  4. | id_cqrlog_main | int(11)       | NO   | PRI | NULL    | auto_increment |
  5. | qsodate        | date          | NO   | MUL | NULL    |                |
  6.  
  7.  

I have one program that randomly loses dates saved in database. It just gives a splash "2022-11-04 is not valid date format Abort/OK"

What is sad is that I have not found any way to reproduce this so that it could be debugged properly.
It just happens, then may be several usage times without a problem and then just randomly pops up again.

Normally restarting program helps right away, do dates are in SQL table OK.

Making addition (any number)  to date in SQL sentence brings it up.
See this issue: https://github.com/ok2cqr/cqrlog/issues/540

I just wanted to ask has anyone had this kind of "valid date format error" with some other FPC program using MySQL?

--
Saku

Zvoni

  • Hero Member
  • *****
  • Posts: 1547
Re: LInux / MySQL date format
« Reply #1 on: November 06, 2022, 01:05:06 pm »
Is qsodate really a date-type, or is it some integer-formatted date?
Looking at your github link i think it might be the second (close to the end of github link where you query qsodate+0)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

OH1KH

  • Jr. Member
  • **
  • Posts: 59
Re: LInux / MySQL date format
« Reply #2 on: November 10, 2022, 06:45:06 am »
Hi!

As seen from 1st message the "qsodate" is SQL type "date"  (YYYY-MM-DD)
When things work normally it looks like YYYY-MM-DD in programs view.

When I look it with command console's mysql:
Code: Text  [Select][+][-]
  1. MariaDB [cqrlog003]> select qsodate from cqrlog_main where id_cqrlog_main=13296;
  2. +------------+
  3. | qsodate    |
  4. +------------+
  5. | 2022-11-04 |
  6. +------------+
  7. 1 row in set (0.001 sec)
  8.  
  9. MariaDB [cqrlog003]> select qsodate+0 from cqrlog_main where id_cqrlog_main=13296;
  10.  
  11. +-----------+
  12. | qsodate+0 |
  13. +-----------+
  14. |  20221104 |
  15. +-----------+
  16. 1 row in set (0.002 sec)
  17.  
  18. MariaDB [cqrlog003]>
  19.  

As seen from added issue link when date is added with zero the output format changes. It does it also with command console's mysql.

As the this program has it's internal SQL console (that uses FPC/SQL unit) that displays query results "as is" it makes me wonder why it does not pass YYYY-MM-DD, but passes YYYYMMDD (when zero added to date).

That really looks like the SQL unit gets randomly to faulty state, but I can not find a way to reproduce this regularly for debugging to be sure it is in SQL unit.
--
Saku

Zvoni

  • Hero Member
  • *****
  • Posts: 1547
Re: LInux / MySQL date format
« Reply #3 on: November 10, 2022, 09:33:56 am »
For testing: have you tried to cast it to date in your SQL?
Just to see what it returns.
Code: SQL  [Select][+][-]
  1. SELECT CAST(qsodate+0 AS DATE) AS testDate FROM cqrlog_main WHERE id_cqrlog_main=13296;
btw: Don't forget, that a Date-Type is basically an INTEGER (or Double if DateTime)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

dsiders

  • Hero Member
  • *****
  • Posts: 726
Re: LInux / MySQL date format
« Reply #4 on: November 10, 2022, 05:36:01 pm »
For testing: have you tried to cast it to date in your SQL?
Just to see what it returns.
Code: SQL  [Select][+][-]
  1. SELECT CAST(qsodate+0 AS DATE) AS testDate FROM cqrlog_main WHERE id_cqrlog_main=13296;
btw: Don't forget, that a Date-Type is basically an INTEGER (or Double if DateTime)

Maybe I'm missing the point here...

Why not use the built-in date functions? Like DATE_ADD().

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add
Preview Lazarus 2.3.0 documentation at: https://dsiders.gitlab.io/lazdocsnext

Zvoni

  • Hero Member
  • *****
  • Posts: 1547
Re: LInux / MySQL date format
« Reply #5 on: November 10, 2022, 05:55:36 pm »
For testing: have you tried to cast it to date in your SQL?
Just to see what it returns.
Code: SQL  [Select][+][-]
  1. SELECT CAST(qsodate+0 AS DATE) AS testDate FROM cqrlog_main WHERE id_cqrlog_main=13296;
btw: Don't forget, that a Date-Type is basically an INTEGER (or Double if DateTime)

Maybe I'm missing the point here...

Why not use the built-in date functions? Like DATE_ADD().

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add
OP‘s problem is that sometimes his program throws an error that „YYYY-MM-DD“ is an invalid dateformat
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

OH1KH

  • Jr. Member
  • **
  • Posts: 59
Re: LInux / MySQL date format
« Reply #6 on: November 11, 2022, 09:16:27 am »
HI !
Thanks for all answers so far!

I do not need "qsodate+0", I just found out that when qsodate returns as completely empty string (see the link to GitHub issue, in 1st message) then making addition to to qsodate at SQL side makes it visible at program's side.

I.E. query should produce "qsodate" as YYYY-DD-MM in program's grid or program's SQL console.
How ever sometimes, randomly, it happens that "qsodate" is completely empty.

This is random and rare, so debugging is difficult. I those cases testing with program's SQL console is the only way to pinpoint something because restarting program usually removes the bug.

Then I found out that adding something to qsodate at SQL side passes it to program display because the format of date changes to one without "-" chars in between. Without adding should  YYYY-MM-DD appear, but the output is empty.
Because programs SQL console is "directly connected" to query result (means that program does not process result before displaying in grid) and command line mysql gives right result, I have strong feeling that FPC's SQL unit has some kind of underlying bug.

That bug may be depended on OS locales. My locales include LC_TIME="fi_FI.UTF-8" and the way SQL saves and program displays date (YYYY-MM-DD) is not the one by locales that is DD.MM.YYYY  Perhaps that makes it sometimes fail.

But as log as I can not catch the bug repeatedly I can not be sure and make a bug report.

I just wanted to know has anyone else ever noticed same kind of results. In that case I would, maybe, put more efforts for tracking it.
--
Saku

Zvoni

  • Hero Member
  • *****
  • Posts: 1547
Re: LInux / MySQL date format
« Reply #7 on: November 11, 2022, 09:28:29 am »
I just wanted to know has anyone else ever noticed same kind of results. In that case I would, maybe, put more efforts for tracking it.
In short: No.

OTOH: Since we don't know any real SQL-Statements of yours, it's still guesswork what it might be.
We don't know if there are any calculations, scalar-functions or window-functions involved which might influence your results
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018