B4J Question [SOLVED] setting up DateTime parms for sqlite

[Peter Lewis]

Hi, I made my initial DB though sqlite studio which allowed me to choose DateTime for a few fields

CREATE TABLE chat (
    logcount          INTEGER  UNIQUE
                               PRIMARY KEY AUTOINCREMENT
                               NOT NULL,
    topic             STRING,
    servertime        DATETIME,
    message           STRING,
    senttime          DATETIME,
    deliveredtime     DATETIME,
    viewedtime        DATETIME,
    fromuser          STRING,
    messageno         STRING,
    messagedts        REAL     UNIQUE,
    reference         STRING,
    image             STRING,
    fromusername      STRING,
    thumbnailfilename STRING
);

Now I want to write the code in my application so that if the file is not there it can create it but according to DButils 2 there are only a few field types avail

Only these data types are available:
INTEGER
is a 64-bit signed integer number.
REAL
is a 64-bit IEEE floating point number.
TEXT
is a string.
BLOB
Binary Large OBject, the value is stored exactly as it was input.
NULL
INTEGER PRIMARY KEY is a special variable type used for identifier ID's. It is a long integer
value beginning with 1 and it is incremented by one each time a new data set is added to the
database.

So there is no DATETIME

I have used text instead but I do not know how that would affect any conversions down the line ie SQL date to ticks

    Dim o As Map
    o.Initialize
    o.Put("logcount", DBUtils.DB_INTEGER)
    o.Put("servertime", DBUtils.DB_TEXT)
    o.Put("message", DBUtils.DB_TEXT)
    o.Put("senttime", DBUtils.DB_TEXT)
    o.Put("deliveredtime", DBUtils.DB_TEXT)
    o.Put("viewedtime", DBUtils.DB_TEXT)
    o.Put("fromuser", DBUtils.DB_TEXT)
    o.Put("messageno", DBUtils.DB_TEXT)
    o.Put("messagedts", DBUtils.DB_REAL)
    o.Put("reference", DBUtils.DB_TEXT)
    o.Put("image", DBUtils.DB_TEXT)
    o.Put("fromusername", DBUtils.DB_TEXT)
    o.Put("thumbnailfilename", DBUtils.DB_TEXT)

Advise would be appreciated, thank you

 

[mcqueccu]

TEXT is good.

 

[KMatle]

Yes. Use text only. I store ticks as a timestamp (datetime.now). Even for numbers I use the text format (except of autoincrement columns)

 

[Peter Lewis]

Yes. Use text only. I store ticks as a timestamp (datetime.now). Even for numbers I use the text format (except of autoincrement columns)

I store the datetimenow as a REAL or Long within b4j

because this is 2021-02-03 10:29:36 , so I will keep it as text. Thanks