0

My Sqlite database has a 'Time' field of type 'INTEGER'

I write Unix time (epoch) to it from a C++ windows service like this:

insert into ServicesData (Time, ...) values (((strftime('%s', 'now') - strftime('%S', 'now') + strftime('%f', 'now')) * 1000), ...);

(see https://stackoverflow.com/a/20478329/460084 "grabbing the number of seconds since the Unix Epoch (%s), subtracting the number of seconds in the current time (%S), adding the number of seconds with decimal places (%f), and multiplying the result by 1000 to convert from seconds to milliseconds")

I then read the date from a .NET Core 3.1 console app like this:

public class ServicesData {
  [Key]
  public long Time { get; set; }
  ... 
  public string ToString() {
    string res="";
    var time = DateTimeOffset.FromUnixTimeMilliseconds(Time).DateTime;
    res+= time.ToString("dd/MM/yy HH:mm:ss") + " ";
    ... return res;
  }
}


I now do a query from my .NET app on the same box and verify that the integer value of ServicesData.Time is the same as that on the database using DB Browser for SQLite

However on calling ToString() the resultant displayed date is one hour FW of the time when the entry was written to the DB !

Why aren't the dates matching ?

(note I tried replacing FromUnixTimeMilliseconds(Time).DateTime with FromUnixTimeMilliseconds(Time).UtcDateTime; but got the same result)

kofifus
  • 17,260
  • 17
  • 99
  • 173
  • current culture difference? – jazb Nov 28 '19 at 05:12
  • possibly or day light saving but I can't see how/where is that set ? or how to fix it ? – kofifus Nov 28 '19 at 05:14
  • i would take a look at the machine settings you are running the code on to get that identified to start with in terms of date-time settings – jazb Nov 28 '19 at 05:17
  • all code both writing and reading are on the same machine, that is why I am confused – kofifus Nov 28 '19 at 05:21
  • It would be a *LOT* easier for everyone (including you a few days later) if you stored ISO dates. SQLite doesn't prefer ISO dates over Unix timestamps, nor are they more accurate. In fact, the Unix timestamp is per *second* and so has lower precision that the ISO string that goes into milliseconds – Panagiotis Kanavos Nov 28 '19 at 17:14
  • In any case, `FromUnixTimeMilliseconds` assumes UTC. If the C++ code stored *local* time when DST rules were in effect, there will be a 1 hour difference and there's nothing that can be done. The data was stored in the wrong way to begin with. – Panagiotis Kanavos Nov 28 '19 at 17:21
  • If you're in the UK, 1970 was permanently on DST, so 1-1-1970 was UTC+1. That's enough to mangle the timestamp calculation – Panagiotis Kanavos Nov 28 '19 at 17:23
  • @PanagiotisKanavos you are probably right, though storing strings is much less efficient ... I did get it to work in the end see below but still not sure why `FromUnixTimeMilliseconds` wan't working – kofifus Nov 28 '19 at 22:22

1 Answers1

1

I am not sure why FromUnixTimeMilliseconds above doesn't work, but this works correctly:

var time = new DateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeKind.Utc)
  .AddMilliseconds(Time)
  .ToLocalTime();
kofifus
  • 17,260
  • 17
  • 99
  • 173
  • 1
    That's equivalent to `FromUnixTimeMilliseconds (Time).ToLocalTime()`. It's the addition of `ToLocalTime()` at the end that makes the difference - but I would personally be pretty nervous about doing that. Do you *really* want the server-local version of that timestamp? – Jon Skeet Nov 29 '19 at 09:19
  • 1
    That doesn't really mean it's the right thing to do. For example, you might read it on that machine, but then return it as a local time to a user - at which point, the local time is pointless. And what happens if later you move the server? I would advise you to think *very* carefully about how you want to handle time zones. – Jon Skeet Nov 30 '19 at 09:11
  • Thanks Jon will do, also do you have any other clues/solutions to the issue? – kofifus Dec 01 '19 at 04:21
  • 1
    Not really, because you haven't described clearly exactly what you're trying to achieve. It looks like you're probably correctly storing a UTC timestamp (although I'm not 100% certain of that) - you then need to think about what you want to do with that afterwards. – Jon Skeet Dec 01 '19 at 08:56