Android Question SQLite Date and Time: Data Storage, Fetch and Display

[mfstuart]

Hi all,
I'm trying to manage/decide how to store dates and times in my barcode app. The data is stored in a tablet SQLite db. Eventually, the data will then have to be JSON'd to a SQL Server database, thru a web service.

Questions:
Should I store in integer Ticks or text as: yyyyMMdd HHMMSS?
Which format is preferred?
(Sorting is no problem in both formats)
How does one set the data in the correct format for:
- storing?
- fetching?
- date and time format for display?

I've played with some Subs to manage all this, but there's gotta be a simpler way to manage B4A/SQLite dates and times.
If anyone has thoughts on this, or can point me to existing resolutions, I would be grateful.

Thanx,
Mark S.


BTW, I think I found a bug in this function I created.
The returning value is different than the incoming value.
They should be the same.

Sub ConvertDateTime(sDateTime As String) As String
    'sDateTime incoming format: yyyyMMdd HHMMSS
    'sDateTime incoming value : '20171231 091011'
    'Idea of this Sub is to convert the format of incoming sDateTime to a display format
    'Hence switching of the DateFormat and TimeFormat
   
    DateTime.DateFormat = "yyyyMMdd"
    DateTime.TimeFormat = "HHMMSS"   
   
    Dim DateTimeParts() As String
    DateTimeParts = Regex.Split(" ",sDateTime)
    Dim sDate As String = DateTimeParts(0)
    Dim sTime As String = DateTimeParts(1)
    Dim sDateTimeTicks As Long = DateTime.DateTimeParse(sDate,sTime)
   
    DateTime.DateFormat = "MM/dd/yyyy"
    DateTime.TimeFormat = "HH:MM:SS"
    'conversion of the date and time values are incorrect:
    'Returning Date Value = 09/30/2018 (different from incoming)
    'Returning Time Value = 09:09:11 (different from incoming)
    Return DateTime.Date(sDateTimeTicks) & " " & DateTime.Time(sDateTimeTicks)
End Sub

 

[DonManfred]

DateTime.TimeFormat = "HH:MM:SS"

MM is Month
mm is Minute

SS is MilliSecond
ss is Second

See http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

 

[klaus]

You may have a look at chapter 5 SQLite databse and more specifically chapter 5.1.9 Date / Time functions in the B4A User's Guide.

 

[mfstuart]

MM is Month
mm is Minute

SS is MilliSecond
ss is Second

See http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Aye, corumba.
I've been so used to typing the HHMMSS in upper case for donkeys years in another development software that I made this typo.
Thanx DonManfred for pointing that out. With the corrections it now returns the data as expected.

Hi klaus - I've been using the B4a Object Browser, which has been very useful to find all the methods and functions of each library, But I will check out the docs as you mention.

Thanx,
Mark S.