Re: [sqlite] Question about date time
Thanks for the reply. I understand. I am going to do some experimenting just to make sure... Regards, -Bill On 9/15/2013 3:13 PM, Petite Abeille wrote: On Sep 15, 2013, at 8:31 PM, William Drago wdr...@suffolk.lib.ny.us wrote: Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? (Date Time seems to be a popular topic at the moment) There is nothing prescriptive in using epoch time. As SQLite doesn't have a dedicated date type, you are free to decide how you want to handle it. There are two main encoding: (1) As a number: Julian date, unix epoch, etc (2) As a string: ISO 8601 co.. Just make sure that your string representation sorts properly. http://www.sqlite.org/lang_datefunc.html The granularity of the date is up to you as well: day, hour, milliseconds, etc. This is more driven by what's convenient for your application. Ditto if this should be split between date time. Depending on the task at hand, you could even require a much more full fledge set of entities: create table if not exists date ( idinteger not null constraint date_pk primary key, year integer not null, month integer not null, day integer not null, day_of_year integer not null, day_of_week integer not null, week_of_year integer not null, constraintdate_uk unique( year, month, day ) ) create table if not exists time ( id integer not null constraint time_pk primary key, hourinteger not null, minute integer not null, second integer not null, constraint time_uk unique( hour, minute, second ) ) And then there are timezones, etc… ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question about date time
All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. Thanks, -Bill ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
On 15 Sep 2013 at 18:13, William Drago wdr...@suffolk.lib.ny.us wrote: All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. I convert everything to seconds since the epoch and have a column with that. All comparisons are done against that value. This is not too difficult in PHP. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
Hi Tim, Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? Say for example, I want to know for the past month what my failure rate was between 11PM and 1AM every day. I'd figure out what 11PM and 1AM is in seconds since the epoch for the 1st of the month and then for each of the next 30 days, then figure out some SELECT statement to use that set of numbers. It seems very convoluted. Anyway, I have a feeling I'm asking this question in the wrong place. I'm not sure this is a SQLite specific question as the answer is probably going to be the same regardless of the DBMS. Thanks, -Bill On 9/15/2013 1:16 PM, Tim Streater wrote: On 15 Sep 2013 at 18:13, William Drago wdr...@suffolk.lib.ny.us wrote: All, Should I put date and time in separate columns if I want to select by time? For example: SELECT * FROM testresults WHERE (status != Pass) AND (23:00 = testtime) AND (testtime = 01:00). I have been reading the documentation, but it just isn't clear to me how I should handle this. I convert everything to seconds since the epoch and have a column with that. All comparisons are done against that value. This is not too difficult in PHP. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about date time
On Sep 15, 2013, at 8:31 PM, William Drago wdr...@suffolk.lib.ny.us wrote: Thanks for the reply. Seconds since the epoch does make a good timestamp. Is that what is normally used to extract data between time periods? (Date Time seems to be a popular topic at the moment) There is nothing prescriptive in using epoch time. As SQLite doesn't have a dedicated date type, you are free to decide how you want to handle it. There are two main encoding: (1) As a number: Julian date, unix epoch, etc (2) As a string: ISO 8601 co.. Just make sure that your string representation sorts properly. http://www.sqlite.org/lang_datefunc.html The granularity of the date is up to you as well: day, hour, milliseconds, etc. This is more driven by what's convenient for your application. Ditto if this should be split between date time. Depending on the task at hand, you could even require a much more full fledge set of entities: create table if not exists date ( idinteger not null constraint date_pk primary key, year integer not null, month integer not null, day integer not null, day_of_year integer not null, day_of_week integer not null, week_of_year integer not null, constraintdate_uk unique( year, month, day ) ) create table if not exists time ( id integer not null constraint time_pk primary key, hourinteger not null, minute integer not null, second integer not null, constraint time_uk unique( hour, minute, second ) ) And then there are timezones, etc… ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users