Re: [sqlite] Question about date & time

[William Drago]

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

Re: [sqlite] Question about date & time

[Petite Abeille]

On Sep 15, 2013, at 8:31 PM, William Drago  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

Re: [sqlite] Question about date & time

[William Drago]

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

[Tim Streater]

On 15 Sep 2013 at 18:13, William Drago  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