Android Question how to format date and time columns in B4XTable

Mario Krsnic · Sep 3, 2020

I work on the application, which should be used by Parkinson ill people to enter their good and bad times (on and off times).

Ich have three columns in the sqlite database. In the first column the date is being saved, in the second one the time of the day. In the third the number should be saved for “good”.

I save the date with this code:

Saving date in Table:

Sub cmdBien_Click
    Try
        Private Query As String
        'Dim RowID As Int

        Query = "INSERT INTO protocol ( Data,Tiempo, bien) VALUES (?,?,?)"
        Dim mojDatum As Long=DateTime.Now
    
        'Dim mojeVrijeme As Long= DateTime.Now
        Starter.SQL1.ExecNonQuery2(Query,Array As Object(mojDatum,mojDatum,1))

        ToastMessageShow("Guardado!", False)    ' confirmation for the user
    
        
    
    Catch LastException
        MsgboxAsync ( LastException.Message,"Fehler"  )
    End Try
End Sub

I show the data in the B4XTable and I would present them in this format:

Date: “"dd.MM.yyyy"

Time: "hh:mm"

I do not manage to show data in this format, if I set DateTime.DateFormat="dd.MM.yyyy"

Then the both columns in the db appear in the format “"dd.MM.yyyy"

If I I set DateTime.DateFormat= "hh:mm"

Then the both columns in the db appear in the format "hh:mm".

Please help me!

Here is code for loading the data:

showing date in table:

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    'Private Button1 As Button
 
    Private B4XTable1 As B4XTable
 
    
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("tabelle")
    Activity.Title="Protocolo"
    
    Try
        DateTime.DateFormat="dd.MM.yyyy"
        'DateTime.TimeFormat="hh:mm"
 
    Dim DateColumn As B4XTableColumn = B4XTable1.AddColumn("Data", B4XTable1.COLUMN_TYPE_DATE)
    DateColumn.Width = 90dip
 
        
        Dim TimeColumn As B4XTableColumn = B4XTable1.AddColumn("Tiempo", B4XTable1.COLUMN_TYPE_DATE)
        TimeColumn.Width = 90dip
        
        
    Dim BienColumn As B4XTableColumn = B4XTable1.AddColumn("bien", B4XTable1.COLUMN_TYPE_NUMBERS)
    BienColumn.Width = 50dip
    

    FillTable
    

    Sleep(50)
    'B4XTable1.SearchField.HintText="Suchen"
    'B4XTable1.SearchField.Update
    
        B4XTable1.SearchField.mBase.Visible = False
    Catch LastException
        MsgboxAsync ( LastException.Message,"error"  )
    End Try
End Sub

Sub FillTable
    Try
        DateTime.DateFormat="dd.MM.yyyy"
        'DateTime.TimeFormat="hh:mm"
        
    Dim Data As List
    Dim rs As ResultSet
    Private Query As String
    
    Data.Initialize
    
    
    Query = "SELECT Data,  Tiempo, bien from protocol "
    rs = Starter.SQL1.ExecQuery(Query)
    Do While rs.NextRow
        Dim row(3) As Object
            
            row(0) = rs.GetString("Data")
            
            
            row(1) = DateTime.time(rs.GetString("Tiempo"))
            row(2) = rs.GetInt("bien")
        
        Data.Add(row)
    Loop
    rs.Close
        B4XTable1.SetData(Data)
    Catch LastException
        MsgboxAsync ( LastException.Message,"Error"  )
    End Try
End Sub

Thanks in advance!
Mario

John Naylor · Sep 3, 2020

I use the following sub quite a bit...

B4X:

public Sub DateFromTo (fromformat As String, toformat As String, dt As String) As String
    
    DateTime.DateFormat = fromformat
    Dim n As Long
    n = DateTime.DateParse(dt)
    DateTime.DateFormat = toformat
    Dim s As String
    s = DateTime.Date(n)
    
    Return s
    
End Sub

You could use that for each row you need to format in your FillTable sub

Mahares · Sep 3, 2020

Mario Krsnic said:
Please help me!

Set up the B4XTable columns like this (notice time COLUMN_TYPE_TEXT:

B4X:

Dim DateColumn As B4XTableColumn = B4XTable1.AddColumn("Data", B4XTable1.COLUMN_TYPE_DATE)
    DateColumn.Width = 190dip   
    Dim TimeColumn As B4XTableColumn = B4XTable1.AddColumn("Tiempo", B4XTable1.COLUMN_TYPE_TEXT)
    TimeColumn.Width = 190dip           
    Dim BienColumn As B4XTableColumn = B4XTable1.AddColumn("bien", B4XTable1.COLUMN_TYPE_NUMBERS)
    BienColumn.Width = 50dip

Then:

B4X:

DateTime.DateFormat="dd.MM.yyyy HH:mm"
    Query = "SELECT Data,  Tiempo, bien from protocol "
    rs = Starter.SQL1.ExecQuery(Query)
    Do While rs.NextRow
        Dim row(3) As Object
        row(0) = rs.GetString("Data")        
        row(1) = DateTime.Time(row(0))
        row(2) = rs.GetInt("bien")     
        Data.Add(row)
    Loop
    rs.Close

See screenshot attached. I hope that is what you are after.

Mario Krsnic · Sep 4, 2020

GymReaper said:
I use the following sub quite a bit...

B4X:

public Sub DateFromTo (fromformat As String, toformat As String, dt As String) As String DateTime.DateFormat = fromformat Dim n As Long n = DateTime.DateParse(dt) DateTime.DateFormat = toformat Dim s As String s = DateTime.Date(n) Return s End Sub

You could use that for each row you need to format in your FillTable sub

Thank you GymReaper, it is a very nice peace of code and I will use it in the future in some other context, to format date and time values.
Here I get the error "java.text.ParseException: Unparseable date: "1598817390741" (at offset 13)", I suppose because I have in the DB long values an not date string.

Mario Krsnic · Sep 4, 2020

Mahares said:

Set up the B4XTable columns like this (notice time COLUMN_TYPE_TEXT:

B4X:

Dim DateColumn As B4XTableColumn = B4XTable1.AddColumn("Data", B4XTable1.COLUMN_TYPE_DATE)
    DateColumn.Width = 190dip  
    Dim TimeColumn As B4XTableColumn = B4XTable1.AddColumn("Tiempo", B4XTable1.COLUMN_TYPE_TEXT)
    TimeColumn.Width = 190dip          
    Dim BienColumn As B4XTableColumn = B4XTable1.AddColumn("bien", B4XTable1.COLUMN_TYPE_NUMBERS)
    BienColumn.Width = 50dip

Then:

B4X:

DateTime.DateFormat="dd.MM.yyyy HH:mm"
    Query = "SELECT Data,  Tiempo, bien from protocol "
    rs = Starter.SQL1.ExecQuery(Query)
    Do While rs.NextRow
        Dim row(3) As Object
        row(0) = rs.GetString("Data")       
        row(1) = DateTime.Time(row(0))
        row(2) = rs.GetInt("bien")    
        Data.Add(row)
    Loop
    rs.Close

See screenshot attached. I hope that is what you are after.

Yes, Mahares, that is what I wanted to have! Thank you very much!

John Naylor · Sep 4, 2020

Mario Krsnic said:
Thank you GymReaper, it is a very nice peace of code and I will use it in the future in some other context, to format date and time values.
Here I get the error "java.text.ParseException: Unparseable date: "1598817390741" (at offset 13)", I suppose because I have in the DB long values an not date string.

Absolutely correct.

Looks like you are using a UNIX timestamp.

Here's another sub for you in that case, just for future use...

B4X:

Sub ConvertDateTimeFromUNIX (d As String) As String

    Dim rv As String
    Dim t As Long
    
    If d.Length>10 Then
        DateTime.DateFormat = "yyyy-MM-dd HH:mm"
    Else
        DateTime.DateFormat="yyyy-MM-dd"
    End If
    
    t=DateTime.DateParse(d)
    
    'Change the date format from mySQL/UNIX compliant
    DateTime.DateFormat = "dd/MM/yyyy HH:mm"
    
    rv=DateTime.Date(t)
    
    'Change the date format back to uk (adjust for your own requirements)
    
    DateTime.DateFormat = "dd/MM/yyyy"

    Return rv
    
End Sub

Mario Krsnic · Sep 5, 2020

GymReaper said:

Absolutely correct.

Looks like you are using a UNIX timestamp.

Here's another sub for you in that case, just for future use...

B4X:

Sub ConvertDateTimeFromUNIX (d As String) As String

    Dim rv As String
    Dim t As Long
   
    If d.Length>10 Then
        DateTime.DateFormat = "yyyy-MM-dd HH:mm"
    Else
        DateTime.DateFormat="yyyy-MM-dd"
    End If
   
    t=DateTime.DateParse(d)
   
    'Change the date format from mySQL/UNIX compliant
    DateTime.DateFormat = "dd/MM/yyyy HH:mm"
   
    rv=DateTime.Date(t)
   
    'Change the date format back to uk (adjust for your own requirements)
   
    DateTime.DateFormat = "dd/MM/yyyy"

    Return rv
   
End Sub

Super, thank you!

Android Question how to format date and time columns in B4XTable

Mario Krsnic

Member

Attachments

John Naylor

Active Member

Mahares

Expert

Attachments

Mario Krsnic

Member

Mario Krsnic

Member

John Naylor

Active Member

Mario Krsnic

Member

Similar Threads