Write and read dates and times to/from an SQLite file

[radsoft]

Hello everyone,

In my project, the user will click the "Save" button on a form which writes the record to the SQLite record id determined previously by last_insert_rowid.

A DateGadget is placed on the form with the code below that the user should be able to modify if required:

Code: Select all

myCalendar = DateGadget(#PB_Any, 80, 20, 150, 20, "%yyyy/%mm/%dd %hh:%ii")

The form has a "Save" button that when pressed, collects the entries in the small number of fields and writes them into the SQLite file using the code below:

Code: Select all

OpenDatabase(sqliteNo, "mySQLiteFile.db", "", "")
SetDatabaseString(sqliteNo, 0, GetGadgetText(myCalendar))
SetDatabaseString(sqliteNo, 1, GetGadgetText(strField1))
SetDatabaseString(sqliteNo, 2, GetGadgetText(strField2))
SetDatabaseString(sqliteNo, 3, GetGadgetText(strField3))
SetDatabaseString(sqliteNo, 4, GetGadgetText(strField4))
SetDatabaseString(sqliteNo, 5, GetGadgetText(strField5))
SetDatabaseLong(sqliteNo, 6, GetGadgetState(cbxFrmEventAttention))     ;CheckBoxGadget gadget
SetDatabaseString(sqliteNo, 7, "idle")
SetDatabaseLong(sqliteNo, 8, lastRecID)
        
DatabaseUpdate(sqliteNo, "UPDATE recordEvents SET eventTime=?, eventStaff=?, eventTitle=?, internalCase=?, externalCase=?, eventDescription=?, attention=?, editStatus=? WHERE id=?")
        
CloseDatabase(sqliteNo)

I'm using a TEXT field in the SQLite file to store the date/time.

The problem is that I don't think I'm handling the date/time part correctly as I've seen the SQLite "datetime" function documented elsewhere and I think I'm supposed to use it. I've had a lot of goes at this with varying results and I'm wondering if someone could give me some guidance please on the correct way to write and read dates and times to and from a SQLite file. Also, another part of the code reads and displays the SQLite records in a ListIconGadget with the most recent record at the top. I might also need to offer the user the choice of sorting the records by date/time in ascending or descending order so I'm needing to get this right from the start.

Any help regarding handling dates and times in SQLite would be appreciated, thank you.

[infratec]

Store date/times as timestamp.

Code: Select all

GetGadgetState()

returns directly the 'timestamp' of the CalenderGadget.
In sqlite use integer as field type for the timestamp.
This will result in fastest calculations.

[Marc56us]

Consider that one day you may need to exchange data with other applications, so store your dates in standard ISO-8601 format.
This format also allows you to sort the date as if it were plain text (i.e. in an ascii editor, spreadsheet, etc.).
You'll convert to a format that's easily readable by the user in the PB application.

Alway follow the reference manual
Date And Time Functions

[infratec]

One more reason to store it as timestamp
You can always use the functions to return it as an ISO string.

Or you can use PBs inbuild FormatDate() to do what you want.

[spikey]

As Marcus says the ISO-8601 format will be the most robust and future proof as far as the database is concerned. For the sake of completeness, I feel the need to mention the Y2038 problem at this point in connection with 32-bit Unix epoch type datestamps. See https://en.wikipedia.org/wiki/Year_2038_problem.

[radsoft]

Thank you to all that responded.

Following your advice, I've changed the SQLite Date field to INTEGER and modified the code to:

Code: Select all

OpenDatabase(sqliteNo, "mySQLiteFile.db", "", "")
SetDatabaseLong(sqliteNo, 0, GetGadgetState(myCalendar))
SetDatabaseString(sqliteNo, 1, GetGadgetText(strField1))
SetDatabaseString(sqliteNo, 2, GetGadgetText(strField2))
SetDatabaseString(sqliteNo, 3, GetGadgetText(strField3))
SetDatabaseString(sqliteNo, 4, GetGadgetText(strField4))
SetDatabaseString(sqliteNo, 5, GetGadgetText(strField5))
SetDatabaseLong(sqliteNo, 6, GetGadgetState(cbxFrmEventAttention))     ;CheckBoxGadget gadget
SetDatabaseString(sqliteNo, 7, "idle")
SetDatabaseLong(sqliteNo, 8, lastRecID)
        
DatabaseUpdate(sqliteNo, "UPDATE recordEvents SET eventTime=?, eventStaff=?, eventTitle=?, internalCase=?, externalCase=?, eventDescription=?, attention=?, editStatus=? WHERE id=?")
        
CloseDatabase(sqliteNo)

Now when I look at the SQLite file with SQLiteStudio, I'm seeing entries in the Date field like "1696781467' which I think is what we're wanting?? Am I on the right track?

Thank you.

[tua]

Yes, you are.

And as you have noticed, you have gained size & speed, but lost the ability to 'read/interpret' the dates in the database with one of the many SQLite DB managers, so you will always require a program or calculator to make sense of those date integers.

[radsoft]

Thank you tua.

I'm now using:

Code: Select all

readDate = GetDatabaseLong(sqliteNo, 1)

when I loop through reading the SQLite data file and:

Code: Select all

AddGadgetItem(ligFrmMain, 0, readID + Chr(10) + FormatDate("%yyyy/%mm/%dd %hh:%ii", readDate))

to populate the ListIconGadget. It seems to be working correctly to me.

[mk-soft]

The datetime data type is also possible. This means that the date is also OK with an external viewer.
For PB, the datetime column type is a quad read, but must be read and written as a string, as datetime is of type numeric in SQLite.

Update
- Add how to set date as value, see cherry

Code: Select all

;-TOP

; Comment : ShowDatabaseItems
; Author  : mk-soft
; Version : v1.03.1
; Create  : 12.02.2017
; Update  : 19.08.2022

EnableExplicit

; ***************************************************************************************

Procedure.s ColumnTypeString(Type)
  Protected r1.s
  Select Type
    Case #PB_Database_Long
      r1 = "Long"
    Case #PB_Database_Quad
      r1 = "Quad"
    Case #PB_Database_Float
      r1 = "Float"
    Case #PB_Database_Double
      r1 = "Double"
    Case #PB_Database_String
      r1 = "String"
    Case #PB_Database_Blob
      r1 = "Blob"
  EndSelect
  ProcedureReturn r1
EndProcedure

; ---------------------------------------------------------------------------------------

Procedure GetTextWidth(Text.s, FontID.i = 0)
  Static image
  Protected result
  
  If Not image
    image = CreateImage(#PB_Any, 1, 1)
  EndIf
  
  If image And StartDrawing(ImageOutput(image))
    If FontID
      DrawingFont(FontID)
    EndIf
    result = TextWidth(Text)
    StopDrawing()
  EndIf
  ProcedureReturn result
EndProcedure

; ---------------------------------------------------------------------------------------

Procedure ClearGadgetColumns(Gadget)
  CompilerIf #PB_Compiler_Version <= 551
    ClearGadgetItems(Gadget)
    While GetGadgetItemText(Gadget, -1, 0)
      RemoveGadgetColumn(Gadget, 0)
    Wend
  CompilerElse
    RemoveGadgetColumn(Gadget, #PB_All)
  CompilerEndIf
EndProcedure

; ---------------------------------------------------------------------------------------

Procedure CountGadgetColumns(Gadget)
  Protected result
  CompilerIf #PB_Compiler_Version <= 551
    While GetGadgetItemText(Gadget, -1, result)
      result + 1
    Wend
  CompilerElse
    result = GetGadgetAttribute(Gadget, #PB_ListIcon_ColumnCount)
  CompilerEndIf
  ProcedureReturn result
EndProcedure

; ---------------------------------------------------------------------------------------

Procedure FitColumnWidth(Gadget, Column = #PB_All)
  Protected columns, rows, col, row, width, max, dx
  Static help_gadget
  
  CompilerIf #PB_Compiler_OS = #PB_OS_Windows
    If Column = #PB_All
      columns = CountGadgetColumns(Gadget) - 1
      For col = 0 To columns
        SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, col, #LVSCW_AUTOSIZE_USEHEADER)
      Next
    Else
      SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, Column, #LVSCW_AUTOSIZE_USEHEADER)
    EndIf
    
  CompilerElse
    rows = CountGadgetItems(Gadget) - 1
    dx = GetTextWidth("X", GetGadgetFont(Gadget))
    If Column = #PB_All
      columns = CountGadgetColumns(Gadget) - 1
      For col = 0 To columns
        For row = -1 To rows
          width = Len(GetGadgetItemText(Gadget, row, col))
          If width > max
            max = width
          EndIf
        Next
        width = max * dx + 4
        SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, col)
        width = 0
        max = 0
      Next
    Else
      For row = -1 To rows
        width = Len(GetGadgetItemText(Gadget, row, Column))
        If width > max
          max = width
        EndIf
      Next
      width = max * dx + 4
      SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, Column)
    EndIf
    
  CompilerEndIf
  
EndProcedure

Procedure ShowDatabaseRows(Gadget, DBase, NbDecimals = 0, Hide = #False)
  Protected result.i, columns.i, index.i, size.i, text.s, fltVal.f, dblVal.d
  
  Repeat ; Do
    If GadgetType(Gadget) <> #PB_GadgetType_ListIcon
      Break
    EndIf
    If Not IsDatabase(DBase)
      Break
    EndIf
    HideGadget(Gadget, Hide)
    ClearGadgetItems(Gadget)
    ClearGadgetColumns(Gadget)
    columns = DatabaseColumns(DBase) - 1
    Dim ColumnType(columns)
    For index = 0 To columns
      ColumnType(index) = DatabaseColumnType(DBase, index)
      text = DatabaseColumnName(DBase, index)
      size = GetTextWidth(text) + 12
      AddGadgetColumn(Gadget, index, text, size)
    Next
    While NextDatabaseRow(DBase)
      text = ""
      For index = 0 To columns
        Select ColumnType(index)
          Case #PB_Database_Float
            fltVal = GetDatabaseFloat(DBase, index)
            text + FormatNumber(fltVal, NbDecimals) + #LF$
          Case #PB_Database_Double
            dblVal = GetDatabaseDouble(DBase, index) ; <- Bug Linux
            text + FormatNumber(dblVal, NbDecimals) + #LF$
          Default
            text + GetDatabaseString(DBase, index) + #LF$
        EndSelect
      Next
      AddGadgetItem(Gadget, -1, text)
    Wend
    FinishDatabaseQuery(DBase)
    HideGadget(Gadget, #False)
    result = CountGadgetItems(Gadget)
  Until #True
  ProcedureReturn result
  
EndProcedure

; ***************************************************************************************

CompilerIf #PB_Compiler_IsMainFile
  
  ; Constant
  Enumeration ;Window
    #Main
  EndEnumeration
  
  Enumeration ; Menu
    #Menu
  EndEnumeration
  
  Enumeration ; MenuItems
    #MenuExitApplication
  EndEnumeration
  
  Enumeration ; Gadgets
    #List
    #Edit
  EndEnumeration
  
  Enumeration ; Statusbar
    #Status
  EndEnumeration
  
  ; Global Variable
  Global ExitApplication
  
  ; Functions
  
  UseSQLiteDatabase()
  
  Procedure CheckDatabaseUpdate(Database, Query$)
    Protected Result = DatabaseUpdate(Database, Query$)
    If Result = 0
      Debug DatabaseError()
    EndIf
    ProcedureReturn Result
  EndProcedure
  
  Procedure CreateDummyDatabase(DBase)
    Protected dbName.s
    If 1
      dbName = GetUserDirectory(#PB_Directory_Downloads) + "dbTest.db"
      CreateFile(0, dbName)
      CloseFile(0)
    Else
      dbName = ":memory:"
    EndIf
    If OpenDatabase(DBase, dbName, "", "")
      CheckDatabaseUpdate(DBase, "CREATE TABLE food (recid INTEGER PRIMARY KEY ASC, name CHAR(50), weight REAL, date DATETIME)")
      CheckDatabaseUpdate(DBase, "INSERT INTO food (name, weight, date) VALUES ('apple', '10.005', current_timestamp)")
      CheckDatabaseUpdate(DBase, "INSERT INTO food (name, weight, date) VALUES ('pear', '5.9', '1900-01-01 12:00:00')")
      CheckDatabaseUpdate(DBase, "INSERT INTO food (name, weight, date) VALUES ('banana', '20.35', '2400-12-31 12:00:00')")
      ; DateTime as quad
      SetDatabaseQuad(DBase, 0, Date())
      CheckDatabaseUpdate(DBase, "INSERT INTO food (name, weight, date) VALUES ('cherry', '10.00', datetime(?, 'unixepoch', 'localtime'))")
    Else
      Debug "Can't open database !"
    EndIf
  EndProcedure
  
  Procedure UpdateWindow()
    Protected x, y, dx, dy, menu, status
    
    menu = MenuHeight()
    If IsStatusBar(#Status)
      status = StatusBarHeight(#Status)
    Else
      status = 0
    EndIf
    x = 0
    y = 0
    dx = WindowWidth(#Main)
    dy = WindowHeight(#Main) - menu - status
    ResizeGadget(#List, x, y, dx, dy)
  EndProcedure
  
  ; Main
  Procedure Main()
    Protected event, style, dx, dy, count
    
    style = #PB_Window_SystemMenu | #PB_Window_MinimizeGadget | #PB_Window_MaximizeGadget | #PB_Window_SizeGadget
    dx = 800
    dy = 600
    
    If OpenWindow(#Main, #PB_Ignore, #PB_Ignore, dx, dy, "Main", style)
      
      ; Menu
      CreateMenu(#Menu, WindowID(#Main))
      MenuTitle("Ablage")
      MenuItem(#MenuExitApplication, "Be&enden")
      
      CompilerIf #PB_Compiler_OS = #PB_OS_MacOS 
        ; Mac default menu
        If Not IsMenu(#Menu)
          CreateMenu(#Menu, WindowID(#Main))
        EndIf
        MenuItem(#PB_Menu_About, "")
        MenuItem(#PB_Menu_Preferences, "")
      CompilerEndIf
      
      ; Gadgets
      #ListIconFlags = #PB_ListIcon_AlwaysShowSelection | #PB_ListIcon_FullRowSelect | #PB_ListIcon_GridLines
      ListIconGadget(#List, 0, 0, dx, dy, "recid", 100, #ListIconFlags)
      
      ; Statusbar
      CreateStatusBar(#Status, WindowID(#Main))
      AddStatusBarField(#PB_Ignore)
      
      UpdateWindow()
      
      ;-Test database
      CreateDummyDatabase(0)
      
      If DatabaseQuery(0, "SELECT * FROM food")
      ;If DatabaseQuery(0, "SELECT * FROM sqlite_master")
        count = ShowDatabaseRows(#List, 0, 3)
        StatusBarText(#Status, 0, "Items: " + count)
      EndIf
      
      FitColumnWidth(#List)
      
      ; Main Loop
      Repeat
        event = WaitWindowEvent()
        Select event
          Case #PB_Event_Menu
            Select EventMenu()
                CompilerIf #PB_Compiler_OS = #PB_OS_MacOS
                  
                Case #PB_Menu_About
                  
                Case #PB_Menu_Preferences
                  
                Case #PB_Menu_Quit
                  ExitApplication = #True
                  
                CompilerEndIf
                
              Case #MenuExitApplication
                ExitApplication = #True
                
            EndSelect
            
          Case #PB_Event_Gadget
            Select EventGadget()
              Case #List
                
            EndSelect
            
          Case #PB_Event_SizeWindow
            Select EventWindow()
              Case #Main
                UpdateWindow()
                
            EndSelect
            
          Case #PB_Event_CloseWindow
            Select EventWindow()
              Case #Main
                ExitApplication = #True
                
            EndSelect
            
        EndSelect
        
      Until ExitApplication
      
    EndIf
    
  EndProcedure : Main()
  
  End
  
CompilerEndIf

[radsoft]

Thank you mk-soft. That helps me nicely with the datetime function.