C# query datetime off sqlite database fails

Question

I am reading a sqlite file in which datetime column data are saved as integer values (INTEGER NO NULL)

DateTime dt=reader.GetDateTime(nColDateTime);

But it emits an error saying that the return value is not in correct format. I try out all other available methods in Datetime class and find only

DateTime dt=DateTime.FromBinary(reader.GetInt64(nColDateTime));

works (as others return exceptions). But the formatted date (as dt.ToShortDateTime()) is incorrect (ie 0042/11/20) I have no idea what this is. I then try this

long d=DateTime.Now.Ticks-reader.GetInt64(nColDateTime);
DateTime dt=new DateTime(d);

It gives me 1970/05/18

Could you help me to get the correct datetime ?

Well you're presumably along the right lines with GetInt64, but you need to know what the integer means. Do you have some sample values along with the date/time they're meant to represent? — Jon Skeet
– Jon Skeet, Commented Dec 14, 2011 at 19:22
Thanks, for your interest, 123958450000000000000, just an example. — Sesama Sesame
– Sesama Sesame, Commented Dec 14, 2011 at 19:27
@Jon, since he shows that subtracting the number from DateTime.Now.Ticks gives a date near 1970, we can assume that this is using the Unix epoch. — John Gietzen
– John Gietzen, Commented Dec 14, 2011 at 19:28
@Sesama: That number won't fit in an Int64, so I don't think that is a valid value. — John Gietzen
– John Gietzen, Commented Dec 14, 2011 at 19:31
+1 for you, and Be easy please, as I said it was just an example. huhu, I just lost my school basics, if I could get back to fill this up and above all formally certified as I fking lost it, I won't make this question. huhu, — Sesama Sesame
– Sesama Sesame, Commented Dec 14, 2011 at 19:46

John Gietzen · Accepted Answer · 2011-12-14 19:36:14Z

1

Your dates are stored in the Unix epoch format.

You probably just want to use:

private static readonly DateTime epoch = new DateTime(1970, 1, 1);

...

var myDate = epoch + TimeSpan.FromTicks(reader.GetInt64(nColDateTime));

For example, when I look at your example above "1970/05/18", I can assume that your date is approximately 5 months, 18 days earlier than today.

Here is how I would retreieve the original value:

(DateTime.Today - new DateTime(1970, 5, 18)).Ticks

Which returns:

13119840000000000

Plugging that into my formula:

new DateTime(1970, 1, 1) + TimeSpan.FromTicks(13119840000000000)

This returns:

2011/07/30

edited Dec 14, 2011 at 19:36

answered Dec 14, 2011 at 19:25

John Gietzen

49.8k32 gold badges151 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sesama Sesame Over a year ago

Thank you Smarty John Gietzen, where can I find information of datetime remarks on different systems ?

John Gietzen Over a year ago

@Sesama: I don't really know. Just ask questions that you may have here on Stack Overfow.

Collectives™ on Stack Overflow

C# query datetime off sqlite database fails

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related