B4J Question [B4X] B4XTable and date fields

[LucaMs]

I'm struggling quite a bit with this topic, date type fields in B4XTable.

I finally think I understand what the problem is and I'm afraid I can't solve it.
The aim would be to automatically read a date DB field (DATE, DATETIME, TIMESTAMP) and display it in a B4XTable column set as COLUMN_TYPE_DATE.

Apart from the problem of detecting the format used in the DB (e.g. yyyy-MM-dd or inverted, dd-MM-yyyy and others), the problem is that if the DB table has "mixed fields", i.e. one declared as DATE and another as DATETIME or TIMESTAMP, both display the date according to the command (not related to B4XTable) DateTime.DateFormat, so I can't get the correct display as in the following image (a tool):

TS - TIMESTAMP
UnaData - DATE
DataConTempo - DATETIME

 

[aeric]

One suggestion, read the first 10 characters and truncate the rest.

 

[LucaMs]

One suggestion, read the first 10 characters and truncate the rest.

You would have to read all the records (or maybe the first "not empty" record) to know which part to take but it would not solve the problem, because the data type of the B4XTable is not (should not be) text (COLUMN_TYPE_TEXT) but date (COLUMN_TYPE_DATE).

 

[aeric]

Second suggestion, depending on type of database, use SQL CAST or CONVERT the date to a single format.

B4XTable is not (should not be) text (COLUMN_TYPE_TEXT) but date (COLUMN_TYPE_DATE)

If I am not mistaken, the Date format in B4XTable should be value of ticks (Long). Then the SQL query should return value in ticks.

 

[LucaMs]

Second suggestion, depending on type of database, use SQL CAST or CONVERT the date to a single format.

The software should manage DBs created by third parties, I cannot modify them.

If I am not mistaken, the Date format in B4XTable should be value of ticks (Long). Then the SQL query should return value in ticks.

The handling is in ticks (long) but the display, as mentioned, depends on DateTime.DateFormat and globally, for all columns.

 

[LucaMs]

It would be necessary to modify B4XTable so that you can set a date format for each column.

 

[aeric]

The software should manage DBs created by third parties, I cannot modify them.

How do you get the data from the DB to display in your B4J app as B4XTable? jRDC? You need to execute an SQL command right?

 

[LucaMs]

How do you get the data from the DB to display in your B4J app as B4XTable? jRDC? You need to execute an SQL command right?

Believe me, the problem is not in those things, I struggled by doing many tests.
The point is that if you pass a long value (ticks) to a date type column, you will always see the date in the format set in the project with DateTime.DateFormat (or with the default value of this).

It is not important whether you read the data from a DB or pass it directly, as you could do with:
Dim lngDate As Long = DateTime.DateParse("2024/02/18")

The real problem is that the display depends on the project's date format and it is not possible to assign a date format to every column (every date-type column, obviously).

 

[LucaMs]

The only solution is that B4XColumn should have a DateFormat property (and obviously the internal code of the B4XTable should take this into account).

Dim Clmn As B4XTableColumn
Clmn.DateFormat = "..."

 

[LucaMs]

Modified B4XTable.

Lines changed:

'Added the DateFormat "field".
    Type B4XTableColumn (Title As String, Id As String, ColumnType As Int, Sortable As Boolean, Searchable As Boolean, _
        Formatter As B4XFormatter, SQLID As String, Width As Int, ComputedWidth As Int, CellsLayouts As List, _
        Panel As B4XView, LabelIndex As Int, DisableAutoResizeLayout As Boolean, InternalSortMode As String, DateFormat As String)

Sub ImplUpdateDataFromQuery:

                    Case COLUMN_TYPE_DATE
                        Dim PrevFormat As String = DateTime.DateFormat
                        DateTime.DateFormat = c.DateFormat
                        lbl.Text = DateTime.Date(rs.GetLong(c.SQLID))
                        DateTime.DateFormat = PrevFormat

This is just a suggestion for Erel, I can't use a custom B4XTable in my project, I have to use the official one.
[It would be better to write the PrevFormat declaration before the loopS]

 

[Mahares]

I'm struggling quite a bit with this topic, date type fields in B4XTable

Don't beat me up today like I beat you up yesterday. It is just a question: Can your problem be handled with one common date format for the 3 dates using say something like: DateTime.Dateformat = "yyyy-MM-dd HH:mm:ss"
Then when you query your data you use strftime function in SQLite. You maintain all 3 dates in your B4XTable as BXTable1.COLUMN_TYPE_DATE.
In essence, you will display on your B4XTable something like this:

 

[LucaMs]

Don't beat me up today like I beat you up yesterday. It is just a question: Can your problem be handled with one common date format for the 3 dates using say something like: DateTime.Dateformat = "yyyy-MM-dd HH:mm:ss"
Then when you query your data you use strftime function in SQLite. You maintain all 3 dates in your B4XTable as BXTable1.COLUMN_TYPE_DATE.
In essence, you will display on your B4XTable something like this:
View attachment 149452

My software, as already mentioned, will have to manage databases created by other people and they may want to display two or even more different date formats, just like in this example:

and as the tool I use (SQLite Expert Personal) manages to do:

I'm (almost) certain that the solution is for the B4XTableColumns to have a DateFormat property (with the simple changes I made to B4XTable the matter seems solved, except that I can't use my own custom B4XTable, so Erel should do it, if he thinks it's correct) .

 

[aeric]

I assume you don't want another suggestion because what you want is not to solve the issue without modifying B4XTable, instead what you actually want is request Erel to modifying B4XTable.

 

[LucaMs]

I assume you don't want another suggestion because what you want is not to solve the issue without modifying B4XTable, instead what you actually want is request Erel to modifying B4XTable.

I did it.
https://www.b4x.com/android/forum/threads/b4x-b4xtablecolumn-dateformat-property.158533/post-973160

I assume that the tool I mentioned (and others) does it that way, it must have a format associated with each column, I don't see any other possibilities.
After all, generally speaking, the more properties each object has, the more flexible and useful it is.

 

[aeric]

I can't use a custom B4XTable in my project, I have to use the official one

You may have your own reason but if for me, I will modify to my own version.

 

[Mahares]

If you do not get your wish from Erel, post your project and we will help you with a workaround and get you something like this without having to modify the code in B4XTable lib:

 

[Magma]

@LucaMs you can go... with what guys suggested by adding an extra column hidden... so you will have the same result at screen.

 

[LucaMs]

@LucaMs you can go... with what guys suggested by adding an extra column hidden... so you will have the same result at screen.

?

I'm (pretty ) sure Erel will add the DateFormat property to the B4XDateColumn, as it is useful and also simple to implement. We just have to see when he will do it; I'm in no rush.