Julian days count starting from noon Greenwich Mean Time on January 1, 4713 BC, proleptic Julian calendar. Technically, to store a correct Julian Day floating point number you would have to take the localtime, convert it to GMT, then get the Julian Day. When you retrieve the Julian Day, you would have to convert back to localtime after converting to calendar format. So if you are feeding the julianday function localtime's, the julianday result is always the local julian day offset and technically does not meet the definition of "Julian Day" as known by astronomers. Furthermore, midnight is always half-way through the previous day ... so midnight January 1, 4713 BC is JD -0.5, not 0.0.
>-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Simon Slavin >Sent: Saturday, 9 August, 2014 19:04 >To: phi...@blastbay.com; General Discussion of SQLite Database >Subject: Re: [sqlite] Checking whether a given date is valid > > >On 10 Aug 2014, at 1:51am, Philip Bennefall <phi...@blastbay.com> wrote: > >> That is what I tried to do in my query examples that I included in the >original message. Based on those, would you say that I am doing it >correctly? > >I didn't figure out what you actually have stored in the database. > >If you have text stored, and are trying to find things stored that aren't >real dates, you should be able to do something like this: > >SELECT * FROM Orders WHERE date(julianday(orderDate)) != orderDate > >If you're still at the planning stage, or can revise your table, it's >usually considered better to store numbers than dates. This makes >comparisons and maths faster. In your case I'd probably store a Julian >Date since you only care about date and not time. > >Simon. >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
