[sqlite] Best Practice: Storing Dates

[Stephan Buchert]

>
> 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
needed and represent times in leap seconds (with "60" in the seconds field).

I still see potential advantages with the numeric (day segmented) timestamp
regarding disk storage, e.g. my data are continuously over 1-2 years with
sub-second sampling. Using time strings I would need all 23 bytes allowed
in the Sqlite date and time functions.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Richard Hipp]

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
>
> SELECT * FROM data WHERE julianday(tstr) BETWEEN julianday(tbegstr) AND
> julianday(tendstr);
>

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.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best Practice: Storing Dates

[Stephan Buchert]

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
julianday(tendstr);

particularly if there is an index on t in the first SELECT;

Sqlite's date and time functions support Julian Day, which is in some
circumstances not the best:

1) With 64 bit floats the resolution is about 1 millisecond which is not
sufficient for some real life technical data.

2) It cannot handle leap seconds, such as the one that will be inserted on
June 30, 2015.

An alternative is a "day segmented time code", e. g.

CREATE TABLE timestamped_data (
   day2000 INTEGER,  --nr of days since Jan 1,2000, 0 UTC
   msec INTEGER,  --nr of milliseconds in day
   usec INTEGER,  --microseconds in msec
...
)
-- to speed up searches in time:
CREATE INDEX ON  timestamped_data (day2000,msec,usec);

day2000 can be stored in 16 bit for contemporary data; usec is optional, 16
bit would be enough, msec of course in 32 bit.

I have an Sqlite extension cds2datestr(day2000,msec,usec) returning a human
readable format (only -MM-ddThh:mm:ss.sss supported) which I would be
happy to share.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Tim Streater]

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 had it right.  If you need to do lots of maths with
> the timestamps store unix epochs as INTEGERs (or Julian dates if you care only
> about date and not time).  If, on the other hand, your database is read
> directly by a human a lot, store the dates as ISO format in TEXT.

I would have thought that storing them as integers and then presenting them in 
a user-selected format is much more user-friendly.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Chris Keilitz]

Thank you for all the responses to my original post. They were all very
helpful in formulating my position/solution: Unless you deal with financial
services, Facebook, Twitter type systems processing multi-million /
billions of records or high volumes / TPS that would be hampered by
text/string conversion and extraordinary date calculations, I think storing
the timestamp in a string format in UTC time, using the ISO 8601 notation
is the right answer for a portable, flexible, usable timestamps e.g.,
-MM-DDThh:mm:ssZ.



I had some fun reading your responses and digging into some of the details.
Only if you're interested, here is my rationale for recommending
text/string format using ISO representation over using an integer in Unix
time.



SQLite and most RDBMS can handle efficiently storing and converting between
ISO 8601 string and integer Unix/POSIX/Epoch timestamp (date/time)
representations. Converting back and forth, for me, isn’t really a deciding
factor.



The biggest consideration factor for me ended up being storage. Unix time
wins over a string representation hands down from a storage perspective, 2
to 3 times less storage. In order to get around the "2038 bug" for Unix
time, you'll need to use an unsigned 4 byte integer (or larger). At
3:14:07UTC Tuesday 19 January, there will have been 2,147,483,647 or 2^32-1
seconds that past since 1 January 1970 – one second later and you would
roll over to a negative number (I think). In order to represent the
date/time in UTC in a text/string format, you'd need at a minimum of 15
bytes (MMDDhhmmssZ) and a full 20 bytes using the full notation
(-MM-DDThh:mm:ssZ). Since SQLite doesn't support unsigned integers and
based on the feedback from Baruch Burstein, I assume SQLite would store the
integer as a 4 byte integer until it rolled past 2^32-1 and then would
expand it to a 6 byte integer. Nonetheless, from a storage perspective it
is a 4-6 byte integer vs. a 15-20 byte string. That seems like a lot per
record, but it really isn't for most uses. You would need to store almost
75,000 timestamps to equate to 1MB, 1 million records for 13MB, and you'd
have store over 76 million timestamps to get to 1GB. That said, if you were
storing a billion records, the text/string format would cost you an extra
931GB. This leaves me in thinking for the general user, storage, even on
mobile devices, probably even in memory databases, storing the timestamp in
a text/string representation won't really be that big of a deal.



I am sure performance comes into play at high volumes/TPS, particularly the
cost to convert strings to integers for calculations and then back to
strings again. To do a thorough evaluation, I would want to dig into
specific implementations and do my own tests.  I just don’t have the time
and don’t think it will be a factor at my relatively low volumes. If any of
you have dug into the performance aspects, I’d be interested in hearing.



Thanks again,



Chris

On Wed, Jan 14, 2015 at 9:57 AM, Doug Nebeker <ad...@poweradmin.com> wrote:

> Whatever format you choose to store it in, I highly recommend storing the
> UTC time.  It might be a little more work, but:
>
> 1. your program can display the correct local time, even if the
> database/app/user is in/changes to another timezone
> 2. you won't have to deal with seeing two 1:30am 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 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
> date/time functions / data types and the ISO 8601 standard and have landed
> on these two options:
>
> 1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or 2. Storing it as
> an INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
> time) - number of seconds since 01/01/1970
>
> 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 store the timestamp and how fast are the conversion routines. The
> application I'm writing won't push any performance boundaries and likely
> won't need to overly worry about storage.  I just want to make the right
> call on data type and format and learn something in the process.
>
> Again, I hope this is an appropriate post for this mailing list. If not, I
> 

Re: [sqlite] Best Practice: Storing Dates

[Simon Slavin]

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 unix epochs as INTEGERs (or Julian dates if you care only 
about date and not time).  If, on the other hand, your database is read 
directly by a human a lot, store the dates as ISO format in TEXT.

None of these formats have any problem-dates coming up, so we don't expect a 
y2k panic for any of them.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Baruch Burstein]

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 store
> the timestamp and how fast are the conversion routines. The application I'm
> writing won't push any performance boundaries and likely won't need to
> overly worry about storage.
>

Just for the reference, to answer your size/performance question: The
timestamp will take 4 bytes of data (excluding headers etc.) per entry
(until 2038, after which it will be 6). The text version will be the length
of the string (20 bytes for ISO8601 with second precision).
Naturally, things like comparing and sorting will be faster with the
timestamp then the text version (there is just less data to compare).
Of course, this is just at the theoretical level. As yo said, your app
probably wouldn't need to worry about this.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Adam Devita]

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,
have the authority to change the time zone or implementation date for their
population, there is a high potential for change. 3rd party time zone rule
libraries can externalize most of the maintenance work without affecting
the core app or the database.

regards,
Adam DeVita

On Wed, Jan 14, 2015 at 12:57 PM, Simon Slavin  wrote:

>
> 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
> comparisons and sorting.
>
> There is no problem with using that format.  However I would advise you to
> make a note in your documentation, and/or to add comments to your code,
> saying what TimeZone these stamps are in.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Doug Nebeker]

Whatever format you choose to store it in, I highly recommend storing the UTC 
time.  It might be a little more work, but:

1. your program can display the correct local time, even if the 
database/app/user is in/changes to another timezone
2. you won't have to deal with seeing two 1:30am 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 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 date/time functions / 
data types and the ISO 8601 standard and have landed on these two options:

1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or 2. Storing it as an 
INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
time) - number of seconds since 01/01/1970

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 store the 
timestamp and how fast are the conversion routines. The application I'm writing 
won't push any performance boundaries and likely won't need to overly worry 
about storage.  I just want to make the right call on data type and format and 
learn something in the process.

Again, I hope this is an appropriate post for this mailing list. If not, I 
apologize.

Thanks!

Chris
___
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

Re: [sqlite] Best Practice: Storing Dates

[Simon Slavin]

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 comparisons and sorting.

There is no problem with using that format.  However I would advise you to make 
a note in your documentation, and/or to add comments to your code, saying what 
TimeZone these stamps are in.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Nigel Verity]

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 that introduces 
unnecessary complexity with the field separators.

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 comparisons and sorting.

Regards

Nige
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Richard Hipp]

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
>> way past 2038,
>
>
> In my experience, having the timestamp in Unix Epoch gives you something
> which can be easily converted by a wide variety of tools, and simplifies
> calculation of time deltas (provided you don't need to account for
> timezones, locale-specific summer/winter time changes, and similar
> absurdities). That said, for humans Unix timestamps are basically just a
> pain in the butt. If your data are there for the software, as opposed to
> the humans, i personally find Unix Epoch simpler to work with. If the data
> are strictly for display/reading by humans, without much app logic tied to
> them, ISO8601 is my preferred form (-MM-DD HH:ii:ss...).
>

SQLite support "unix time" (seconds since 1970) and ISO8601 and also
Julian Day number - the number of days (including fractional days)
since noon in Greenwich on November 24, 4714 B.C.  Use whichever
format seems appropriate.  Note that all three formats sort in time
order.  The date and time functions in SQLite will easily convert
between all three formats.

Please familiarize yourself with ISO8601.  Consider using it yourself
when communicating dates, instead of YY-MM-DD or MM/DD/ or various
other formats which are potentially ambiguous and which often do not
sort in time order.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Stephan Beal]

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 Epoch gives you something
which can be easily converted by a wide variety of tools, and simplifies
calculation of time deltas (provided you don't need to account for
timezones, locale-specific summer/winter time changes, and similar
absurdities). That said, for humans Unix timestamps are basically just a
pain in the butt. If your data are there for the software, as opposed to
the humans, i personally find Unix Epoch simpler to work with. If the data
are strictly for display/reading by humans, without much app logic tied to
them, ISO8601 is my preferred form (-MM-DD HH:ii:ss...).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best Practice: Storing Dates

[Chris Keilitz]

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
date/time functions / data types and the ISO 8601 standard and have landed
on these two options:

1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or
2. Storing it as an INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
time) - number of seconds since 01/01/1970

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 store
the timestamp and how fast are the conversion routines. The application I'm
writing won't push any performance boundaries and likely won't need to
overly worry about storage.  I just want to make the right call on data
type and format and learn something in the process.

Again, I hope this is an appropriate post for this mailing list. If not, I
apologize.

Thanks!

Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users