[sqlite] Best Practice: Storing Dates

> > This is also fast: SELECT * FROM data WHERE tstr BETWEEN tbegstr AND > tendstr; And it works just as well if dates are in the ISO8601 format. Aha, yes, thanks, this is certainly a better SELECT than converting string data to numeric Julian Days. And the string can have as much resolution as

Re: [sqlite] Best Practice: Storing Dates

On 1/17/15, Stephan Buchert wrote: > If selecting rows according to a date/timestamp is ever needed, numeric > time stamps are clearly advantageous, e.g. > > SELECT * FROM data WHERE t BETWEEN julianday(tbegstr) AND > julianday(tendstr); > > is much more efficient than > >

[sqlite] Best Practice: Storing Dates

If selecting rows according to a date/timestamp is ever needed, numeric time stamps are clearly advantageous, e.g. SELECT * FROM data WHERE t BETWEEN julianday(tbegstr) AND julianday(tendstr); is much more efficient than SELECT * FROM data WHERE julianday(tstr) BETWEEN julianday(tbegstr) AND

Re: [sqlite] Best Practice: Storing Dates

On 14 Jan 2015 at 23:13, Simon Slavin wrote: > On 14 Jan 2015, at 10:40pm, Baruch Burstein wrote: > >> Of course, this is just at the theoretical level. As yo said, your app >> probably wouldn't need to worry about this. > > I think a previous poster

Re: [sqlite] Best Practice: Storing Dates

Sent: Wednesday, January 14, 2015 7:09 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Best Practice: Storing Dates > > I'm a new sqlite user and new to this mailing list. I hope this question > is appropriate. > > I am writing an application that needs to track a ti

Re: [sqlite] Best Practice: Storing Dates

On 14 Jan 2015, at 10:40pm, Baruch Burstein wrote: > Of course, this is just at the theoretical level. As yo said, your app > probably wouldn't need to worry about this. I think a previous poster had it right. If you need to do lots of maths with the timestamps store

Re: [sqlite] Best Practice: Storing Dates

On Wed, Jan 14, 2015 at 3:09 PM, Chris Keilitz wrote: > Since sqlite and most RDMS implementations have functions to convert to and > from both options and using a LONG should allow the date/time to function > way past 2038, it seems it comes down to how many bytes it takes to

Re: [sqlite] Best Practice: Storing Dates

For the data collection systems we use we store only UTC in the database. The application can translate times to the appropriate time zone and format for the user as required. This variable complexity needs to be controlled into one layer of your program. Since governments, even some city ones,

Re: [sqlite] Best Practice: Storing Dates

on the day that day light savings kicks in -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Chris Keilitz Sent: Wednesday, January 14, 2015 7:09 AM To: sqlite-users@sqlite.org Subject: [sqlite] Best Practice: Storing Dates I'm a new

Re: [sqlite] Best Practice: Storing Dates

On 14 Jan 2015, at 5:53pm, Nigel Verity wrote: > I generally just use a fixed-length 14-character string to store the date and > time in MMDDHHMMSS format. It accommodates any time stamp across a 10,000 > year timespan and also supports simple date/time

Re: [sqlite] Best Practice: Storing Dates

Hi Interesting discussion on the best way to store date/time fields. Unix time certainly has advantages for portability but, I agree, it can be a pain to convert, depending on which applications and tools you are interfacing with. ISO8601 is fine if you just need a local time stamp, but even

Re: [sqlite] Best Practice: Storing Dates

On 1/14/15, Stephan Beal wrote: > On Wed, Jan 14, 2015 at 2:09 PM, Chris Keilitz wrote: > >> Since sqlite and most RDMS implementations have functions to convert to >> and >> from both options and using a LONG should allow the date/time to function >>

Re: [sqlite] Best Practice: Storing Dates

On Wed, Jan 14, 2015 at 2:09 PM, Chris Keilitz wrote: > Since sqlite and most RDMS implementations have functions to convert to and > from both options and using a LONG should allow the date/time to function > way past 2038, In my experience, having the timestamp in Unix

[sqlite] Best Practice: Storing Dates

I'm a new sqlite user and new to this mailing list. I hope this question is appropriate. I am writing an application that needs to track a timestamp - date + time down to the seconds. I'd like to store the date/time in a standard, efficient, usable, portable format. I have looked over the sqlite