search

antares-sql / antares

A modern, fast and productivity driven SQL client with a focus in UX
https://antares-sql.app
MIT License
1.86k stars 128 forks source link

Datetime columns do not show the correct info with SQLite #667

Open hvanbrug opened 1 year ago

hvanbrug commented 1 year ago

I have an SQLite database with some DATETIME columns in one of the tables, eg. Name: LastUpdated Type: DATETIME Length: 0 Allow NULL: false Default: '0'

When the date is stored in there, it is stored as unix epoch eg. 2459067.57491964 (which is 2020-08-06 01:47:53) I noticed that all of my DATETIME columns show up with a bogus value. They all show: 1969-12-31 16:40:59.06700000

I am using: Antares - SQL Client v0.7.16 - from the Microsoft store SQLite v3.40.0 Windows_NT 10.0.19045 x64

image

Fabio286 commented 1 year ago

Hi @hvanbrug,

can you give me some more information? Because i find that 2020-08-06 01:47:53 is 1596671273 in unix timestamp, not 2459067.57491964.

hvanbrug commented 1 year ago

Oops, I got mixed up. The date and time are saved as a Julian day number + time. My apologies. To be more specific, my program that uses this database is written in C# and to get the date I use the built-in conversion datetime.ToOADate() + 2415018.5, which changes my datetime object to the OLE automation date, and then offsets it to julian using the constant. I hope that clears up the confusion. Please let me know if there is more I can help with.

Fabio286 commented 1 year ago

Can you provide me a db with a table containing dates in this format?