Re: [sqlite] Date datatype
On Wed, Jan 21, 2009 at 11:30:58AM -0500, Igor Tandetnik wrote:
> One advantage of the string format is that it's visible to the "naked
> eye" - when working with the database using generic tools (e.g. for
> administration or troubleshooting). It's a pain to run ad-hoc queries
> when the database stores dates as, say, Julian days (which just look
> like huge numbers, all alike).
And using international date format also allows you to use
LIKE/GLOB/REGEXP to efficiently express BETWEEN:
SELECT ... FROM ... WHERE tstamp LIKE '2007-08-%' ...;
Expressing the same using seconds since the Unix epoch is more
complicated:
SELECT ... FROM ... WHERE tstamp BETWEEN
strftime('%s', '2007-08-01') AND
strftime('%s', '2007-08-31') ...;
OTOH, using seconds since the Unix epoch too makes some computations
simpler. You could always store timestamps both ways.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Paolo Pisati wrote: > Igor Tandetnik wrote: >> If you use -MM-DD format consistently, then simple string >> comparison just happens to give the same results as date comparison. >> > i'll take this route, but is it the best choice performance-wise? Two other good choices well supported by built-in functions are to store a Julian day as a floating point value, or a number of seconds since Unix epoch as integer. Comparison is probably slightly faster on these than with strings (but I suggest you test it to make sure, if you believe performance is critical). If you need to do a lot of date arithmetic like getting the next day, then numeric formats will probably also be faster and easier (you could simply add 1 or 86400 to existing value). But if you need calculations like "same date next month", then you would have to go through strftime anyway, which I suspect works about the same for all formats (but again - if it matters, test). One advantage of the string format is that it's visible to the "naked eye" - when working with the database using generic tools (e.g. for administration or troubleshooting). It's a pain to run ad-hoc queries when the database stores dates as, say, Julian days (which just look like huge numbers, all alike). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Igor Tandetnik wrote: > If you use -MM-DD format consistently, then simple string comparison > just happens to give the same results as date comparison. > i'll take this route, but is it the best choice performance-wise? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
It depends on the purpose for which you are using SQLite. It sure makes it ugly trying to convert an existing application TO SQLite. Oracle-like TO_DATE and TO_CHAR functions would be a big help. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, January 21, 2009 10:57 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date datatype Hoover, Jeffrey wrote: > You are comparing the values as strings. > > Instead, format your dates as -MM-DD and use the date function to > convert strings to dates for comparison: If you use -MM-DD format consistently, then simple string comparison just happens to give the same results as date comparison. Igor Tandetnik ___ 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] Date datatype
Hoover, Jeffrey wrote: > You are comparing the values as strings. > > Instead, format your dates as -MM-DD and use the date function to > convert strings to dates for comparison: If you use -MM-DD format consistently, then simple string comparison just happens to give the same results as date comparison. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
that's what date masks are for.. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, January 21, 2009 10:56 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date datatype Hoover, Jeffrey wrote: > Seems the date function could use a lot of work. > > Be nice if it understood some other formats, too, such as 02-JAN-09 Is that January 2nd, 2009 or January 9th, 2002? > or 11/17/2004... If it were 11/12/2004 instead, would it be December 11th or November 12th? Igor Tandetnik ___ 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] Date datatype
Hoover, Jeffrey writes: > ... > Be nice if it understood some other formats, too, such as 02-JAN-09 or > 11/17/2004... But both those two examples are potentially ambiguous !! MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Hoover, Jeffrey wrote: > Seems the date function could use a lot of work. > > Be nice if it understood some other formats, too, such as 02-JAN-09 Is that January 2nd, 2009 or January 9th, 2002? > or 11/17/2004... If it were 11/12/2004 instead, would it be December 11th or November 12th? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Sorry about my previous post. I was wrong. It appears that the DATE
function is exceptionally rigid.
You MUST use 2-digit months and 2-digits days, using a leading zero for
values < 10. Probably need a 4-digit year, too.
Seems the date function could use a lot of work.
Be nice if it understood some other formats, too, such as 02-JAN-09 or
11/17/2004...
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hoover, Jeffrey
Sent: Wednesday, January 21, 2009 10:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date datatype
You are comparing the values as strings.
Instead, format your dates as -MM-DD and use the date function to
convert strings to dates for comparison:
select date from envelope where date > date('2009-01-20') limit 3;
here are some examples:
sqlite> select date('2009-07-01') where
date('2009-07-01')>date('2009-06-01');
2009-07-01
sqlite> select date('2009-07-01') where
date('2009-07-01')mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati
Sent: Wednesday, January 21, 2009 6:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Date datatype
It seems i'm having an hard time with dates in sqlite:
sqlite> .schema
CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT
NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL,
`delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL,
`rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL,
`user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res`
int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL,
`vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL);
sqlite> select date from envelope where date > '2009/01/20' limit 3;
2009/1/7
2009/1/7
2009/1/7
why?
--
bye,
P.
___
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
You are comparing the values as strings.
Instead, format your dates as -MM-DD and use the date function to
convert strings to dates for comparison:
select date from envelope where date > date('2009-01-20') limit 3;
here are some examples:
sqlite> select date('2009-07-01') where
date('2009-07-01')>date('2009-06-01');
2009-07-01
sqlite> select date('2009-07-01') where
date('2009-07-01')mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati
Sent: Wednesday, January 21, 2009 6:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Date datatype
It seems i'm having an hard time with dates in sqlite:
sqlite> .schema
CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT
NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL,
`delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL,
`rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL,
`user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res`
int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL,
`vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL);
sqlite> select date from envelope where date > '2009/01/20' limit 3;
2009/1/7
2009/1/7
2009/1/7
why?
--
bye,
P.
___
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] Date datatype
Sqlite has no date type. Use a floating point number and the Sqlite date functions. Add your own ones to get extra functionality. Paolo Pisati wrote: > It seems i'm having an hard time with dates in sqlite: > > sqlite> .schema > CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT > NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, > `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, > `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, > `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` > int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, > `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); > > sqlite> select date from envelope where date > '2009/01/20' limit 3; > 2009/1/7 > 2009/1/7 > 2009/1/7 > > why? > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
On 21/01/2009 10:09 PM, Paolo Pisati wrote: > It seems i'm having an hard time with dates in sqlite: > > sqlite> .schema > CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT > NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, > `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, > `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, > `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` > int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, > `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); > > sqlite> select date from envelope where date > '2009/01/20' limit 3; > 2009/1/7 > 2009/1/7 > 2009/1/7 > > why? Because "1/7" > "01/20" ... think about it, "1" > "0" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
I had a tough time myself with dates. Hope the following helps. Dates are actually stored in SQLite in -MM-DD format, with leading 0 and they are stored as strings. What helped me is that I do the date comparison as is in the database - even though they are stored as strings the format is consistent, hence 2009-01-21. > 2009-01-20 If I need to display the date it is a simple matter to convert the string to MM/DD/. --Original Message-- From: Paolo Pisati Sender: sqlite-users-boun...@sqlite.org To: General Discussion of SQLite Database ReplyTo: General Discussion of SQLite Database Sent: Jan 21, 2009 06:50 Subject: Re: [sqlite] Date datatype MikeW wrote: > > It's a string comparison, '2009/' is identical in each case > but '1/7' > '01/20' since '1' > '0' > i suspected it, ok. > See also http://www.sqlite.org/lang_datefunc.html > that page shows how to convert date in different formats: does that mean that i have first to convert the date in a unix timestamp and then do a numercial comparison? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Timothy A. Sawyer, CISSP Managing Director MBD Solutions Phone: (603) 546-7132 Web: http://www.mybowlingdiary.com Email: tsaw...@mybowlingdiary.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
MikeW wrote: > > It's a string comparison, '2009/' is identical in each case > but '1/7' > '01/20' since '1' > '0' > i suspected it, ok. > See also http://www.sqlite.org/lang_datefunc.html > that page shows how to convert date in different formats: does that mean that i have first to convert the date in a unix timestamp and then do a numercial comparison? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date datatype
Paolo Pisati writes: > > It seems i'm having an hard time with dates in sqlite: > > sqlite> .schema > CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT > NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, > `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, > `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, > `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` > int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, > `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); > > sqlite> select date from envelope where date > '2009/01/20' limit 3; > 2009/1/7 > 2009/1/7 > 2009/1/7 > > why? > It's a string comparison, '2009/' is identical in each case but '1/7' > '01/20' since '1' > '0' See also http://www.sqlite.org/lang_datefunc.html MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date datatype
It seems i'm having an hard time with dates in sqlite: sqlite> .schema CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL, `delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL, `rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL, `user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res` int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL, `vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL); sqlite> select date from envelope where date > '2009/01/20' limit 3; 2009/1/7 2009/1/7 2009/1/7 why? -- bye, P. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
