DateTime issues / advices

Targets Linux
1

Hello guys,

Today I checked one of my console apps on one of the server and it seems that I found one weird issue on the date time side , it seems that the date is always

2001/01/01 00:33:44

But for example if I go on the server side and I check the date , at the current test time I got this as server date time

Wed Feb 14 09:25:24 EET 2024

On the code side on Date I get the standard code to generate the date time

Var d As DateTime
d = DateTime.Now

And the standard DB record

row.Column("LogTimestamp").DateTimeValue = d

I assume that the app was build on an older version of XOJO , I assume the previous one and no idea what could have happened .

Any ideas ?

So far based on the weird timestamp it seems like a system date error or something else as the dateTime is always the same, no matter on what date it logged the data and at a random time.

I will have to build using the latest XOJO and see if the issue still happens.

Thanks

2

Is it possible that this Column is storing a Unix Timestamp? Does it work if you store the SecondsFrom1970 instead?

row.Column("LogTimestamp").DoubleValue = d.SecondsFrom1970
3

Hello Sascha, honestly this the first time it is happening, and I have no idea where the issue could be, so far the database is a SQLite Database and all columns that handle date are DATETIME type so normally all should be handled properly as I use the same code in other places and it works.

I’ll do a test and let you know

4

This suggests it could be a Unix Timestamp. Just guessing. :slight_smile:

5

What DBMS, what column definition?

Do experiments with a test DB table mimicking the problem and using an updated Xojo (lots of problems with some rows in the past)

By the way, usually we don’t pass “log date/times” to be written, we usually let the server do it using proper columns and server functionality to stamp the time there.

6

What @Rick_Araujo refers to is using prepared SQL Statements instead of the Xojo Solution. Like in

UPDATE tablename SET (LogTimestamp,...) VALUES (NOW(),...) WHERE...
7

Sometimes less than that, all we need is to define a proper column as (in MySQL)

CREATE TABLE my_table (
  log_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
1 Like
8

Well in this case for my solution I do need to log that data and I guess the timestamp column name was the confusion here but all have defined usage. I did updated the app which I build using the latest xojo and i do get same issue so I will dig more into this as it is strange

Thanks

9

I don’t think so. You did not answer the questions, but I will assume you can investigate where the error is by yourself… probably somewhere between the column definition and the Xojo code. :smile:

Probably if I knew what DBMS we are talking about, and could see the column definition, and could see all the Xojo code touching that record, I would know better. There’s some flaw there, and probably it is not strange… maybe not easily noticeable, but not strange.

Happy hunting. :laughing:

10

Sorry you do have the details in the previous post , i did mentioned that it is SQLite and DateTime column

Thanks

11

Maybe we have a mix-match between API1 and API2? How about:

row.DateTimeColumn("LogTimestamp").DateTime = d
12

I see, I’ve lost it. My mind was set to “A server” from the original post.

13

I have to say, that I was also wondering which server this was.

By the way, SQLite has no DATETIME type. Defining your column as DATETIME gives that column numeric affinity, which may affect matters. You may do better to define the column as type TEXT.

I know Xojo offers DATETIME for SQLite, but personally I prefer to store SecondsFrom1970 as an integer. That way I don’t have to worry about whether it’s Summer Time or not, or the Time Zone.

1 Like
14

My guess is that SQLite (you mention is old) was either created with another program or Xojo when there was no DateTime as we know today. As a result the difference in affinity makes the database just to store the same date.

With SQLite I usually see people storing seconds, SQLDate or SQLDateTime. 2001/01/01 00:33:44 is similar but not the same as SQLDateTime.

15

I doubt as the app was created like 4 months ago and using the previous release of XOJO so that should not be an issue. I guess there is something to do with the VPS where is at as that is to weird.

I do have same on several places and they work fine, same code same db identical setup. I just checked the other places

16

I store human readable contents. Preferentially normalized to UTC to avoid DST mess.
Sometimes I store local time (includes DST) mostly for reports, not calculations.
Looking at rows with numbers as dates makes some works, inspections, and even some calculations harder, and they are useless for some servers when using server functions to manage date/time calculations.

17

Sorry, when I read old, I expected years ago.

Did you create the app?
Did you use SQL commands to create the SQLite database or you used another tool?
Do you expect to save on the database the date as 2001/01/01 or that is simple the way you are pulling the information from the database

18

yes and the timestamp that you see in that column supposed to show whenever the api is called and to record some specific data, all code is XOJO only from DB creation, app and so on.

the VPN is on Vultr and it is running on Debian 12 , no idea if that is related but I noticed that now while I was checking for some logs .

Thanks

19

How do you get/see that value?

Can you create a sample project that creates the SQLite, save the information and pull the information?

20

putting it is simple Xojo db.AddRow and that database row .

As for the value that I see , I use SQLPro for SQLite app to see the database.