I have datetime as string like “02/03/2018 21:24”.
I’m trying to store it to SQLite database where I have created datetime field.
This is how I try to insert date:
// arvot(1) = "02/03/2018"
// arvot(2) = "21:24"
dim p() As String = Split(arvot(1),"/")
dim t() As String = Split(arvot(2),":")
Dim d1 As New Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), 0)
Dim d2 As New Xojo.Core.Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), Xojo.Core.TimeZone.Current)
dim lrow As new DatabaseRecord
lrow.Column("date1") = d1
lrow.Column("date2") = d2
vaakadb.InsertRecord("table", lrow)
This Xojo.Core.Date does not even build but Date does?
What is the best way to insert this date to sqlite?
Do I need DateTime field if I store epoch time as integer, or just integer field?
I’m a bit confused with this
I have datetime as string like “02/03/2018 21:24”.
I’m trying to store it to SQLite database where I have created datetime field.
This is how I try to insert date:
// arvot(1) = "02/03/2018"
// arvot(2) = "21:24"
dim p() As String = Split(arvot(1),"/")
dim t() As String = Split(arvot(2),":")
Dim d1 As New Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), 0)
Dim d2 As New Xojo.Core.Date(Val(p(2)), Val(p(1)), Val(p(0)), Val(t(0)), Val(t(1)), Xojo.Core.TimeZone.Current)
dim lrow As new DatabaseRecord
lrow.Column("date1") = d1
lrow.Column("date2") = d2
vaakadb.InsertRecord("table", lrow)
This Xojo.Core.Date does not even build but Date does?
What is the best way to insert this date to sqlite?
Do I need DateTime field if I store epoch time as integer, or just integer field?
I’m a bit confused with this
Thanks
Jukka[/quote]
You could just store seconds from epoch and restore it from that. Dont forget to store the time zone if youre not using GMT!
There are of course several ways to save a date and it depends on the use that you want to make. For my part, I often use a field with TotalSeconds as REAL and another one as TEXT only used for search.
This approach has the following advantages:
easily sort the Date field by TotalSeconds
easily find a range between date1.TotalSeconds and date2.TotalSeconds
display the date according to customer wishes (d.ShortDate, d.LongDate,…)
The second field allows you to save the date in different formats and therefore:
build a fast search engine
allow the user to find the data without having to know the format recorded in the db
example for the search field: “<02/03/2018 21:24><March 2, 2018 21:24><2018-03-02 21:24:00>”
You can of course use d.SecondsFrom1970 instead of d.TotalSeconds.
First off SQLite does not have a DATE or DATETIME datatype. Regardless of the Xojo framework involved.
You should store Date/Time values in a manner that is consistent with not only SQLite but other DB engines as well (not only is it a good habit, but it makes things easier should you scale up to something else later)
A common misconceptions seems to be that just because the Database framework in Xojo indicates a datatype does not mean that SQLite has it as a native type (technically SQLite has NO “Datatypes”, but Affinitiys instead)
In italy have the same format :
dd/mm/yyyy or dd/mm/yyyy
I use this system
In textfield have a mask : “##/##/####”, cuetext “//____” and in lostfocus event use ‘parsedate’ method
Sub LostFocus() Handles LostFocus
if me.text <> "" then
if isValidDate(me.text) = false then
msgbox("La data indicata non una data valida!")
me.text = ""
me.SetFocus
end if
end if
End Sub
method ‘isValidDate’ code :
Public Function isValidDate(strDate as String) as Boolean
// verifica se una data valida e ritorna true se valida o false se non valida
if len(strDate) < 10 then
return false
else
Dim mDate as Date
if ParseDate(strDate, mDate) then
return true
else
return false
end if
end if
End Function
Method for translate string to date ( locale format XOJO use control panel date/time settings )
Public Function StringToDate(extends s as String) as Date
Dim mdate as Date
if ParseDate(s, mDate) then
return mdate
end if
End Function
Public Function italiadata(datastring as String) as String
' converte una data italiana in data SQL
if datastring.trim = "" then
return ""
end if
dim giorno as string
dim mese as string
dim anno as string
dim stringadata as string
dim datastringa() as string
datastringa() = split(datastring,"/")
giorno = datastringa(0)
mese = datastringa(1)
anno = datastringa(2)
stringadata = anno + "-" + mese + "-" + giorno
' il formato restituito sar ANNO-MM-GG
return stringadata
End Function
From date format to italian string date format
Public Function dataitalia(Extends data as date) as String
if data is nil then
data = new date
dim giorno as integer = data.Day
dim mese as Integer = data.Month
dim anno as Integer = data.Year
dim ritorno as string = protocollo(giorno,2,"0") + "/" + protocollo(mese,2,"0") + "/" + protocollo(anno,4,"0")
Return ritorno
else
dim giorno as integer = data.Day
dim mese as Integer = data.Month
dim anno as Integer = data.Year
dim ritorno as string = protocollo(giorno,2,"0") + "/" + protocollo(mese,2,"0") + "/" + protocollo(anno,4,"0")
Return ritorno
end if
End Function
Public Function protocollo(numero as variant, lunghezza as integer, fill as string) as String
' riempie il numero di x spazi vuoti all'inizio...
dim numerotxt as string
dim lungo as integer
dim riempi as string
dim riempito as string
dim pos as integer
' 6 caratteri vuoti
riempi = ""
for pos = 1 to lunghezza
riempi = riempi + fill
next
numerotxt = trim(str(numero))
lungo = len(numerotxt)
riempito = left(riempi,lunghezza-lungo)
return riempito + numerotxt
End Function
I always convert dates to SecondsFrom1970 and store as double in SQLite for just the reasons that Dave S gave above. Only convert to a human-readable format when there’s a human asking for it.
Creo que estoy a punto de volverme loco con este lo.
No puede ser que algo tan simple como cambiar un tipo de dato String a Date o viceversa sea tanto problema. Son muchas lneas de cdigo y hasta ahora no me ha resultado nada.
Logre poner la fecha actual en un TextField con el siguiente cdigo:
// Coloca la fecha de hoy en el textfield con valor de fecha shortdate
Dim d As New Date
Dim displayDate As String = d.ShortDate //Convierte en String la variable d
window1.FechaFld.text = displayDate
//
Ahora, no se que hacer para guardar el valor String que captura el usuario como un valor tipo fecha en una tabla SQLite.
Que debo hacer ahora ? cranme que he intentado todo.
Use SQLDATE/SQLDATETIME format to store you Dates (as strings)
Dim d As New Date
Dim displayDate As String = d.SQLDate // Convert the variable d to String
window1.FechaFld.text = displayDate
Dim anotherDate as Date
anotherDate.SQLDate = displayDate // convert from SQLDate format BACK to Xojo Date type
Part of the issue here is that SQLite has no DATETIME type. Its types are INTEGER, REAL, TEXT, BLOB, or as NULL. See: https://www.sqlite.org/datatype3.html for details.
[quote]
This approach has the following advantages:
easily sort the Date field by TotalSeconds
easily find a range between date1.TotalSeconds and date2.TotalSeconds
display the date according to customer wishes (d.ShortDate, d.LongDate,…)[/quote]
This is much the best approach, for the reasons given. Instead of trying to store a date in some text format that may not suit the user, store it in seconds and only for display convert to the user’s preferred format.
