Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:08 PM, Clay Dowling wrote: strftime(buffer, size, "%Y-%m-%sT%H:%M:%S", now); SOAP uses the same format for date/time information, and I've been neck deep in SOAP for the last few weeks. I note that the newly released SQLite 3.2.0 addresses this issue. The "T" is now allowed. Yay for progress! Regards, David
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 4:27 PM, Kurt Welgehausen wrote:
Well, I might as well use the substr() function, then ...
Yes, unless you think %m is more expressive than 6,2 or
you want to extract more than one component.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
2 2005-03-17T17:22:30
sqlite> select strftime('On %m/%d at %H:%M', dd) Arrival_time from
...> (select substr(d,1,10)||' '||substr(d,-8,8) dd from datetest) ;
Arrival_time
-
On 03/17 at 16:21
On 03/17 at 17:22
I do think that it's more expressive than 6,2, but not than
"substr(d,1,10)||' '||substr(d,-8,8)".
Regards,
David
Re: [sqlite] Should Unary + Cast to a Number?
> Well, I might as well use the substr() function, then ...
Yes, unless you think %m is more expressive than 6,2 or
you want to extract more than one component.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
2 2005-03-17T17:22:30
sqlite> select strftime('On %m/%d at %H:%M', dd) Arrival_time from
...> (select substr(d,1,10)||' '||substr(d,-8,8) dd from datetest) ;
Arrival_time
-
On 03/17 at 16:21
On 03/17 at 17:22
Regards
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:59 PM, Kurt Welgehausen wrote:
Sorry, I misunderstood the context.
No problem.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
sqlite> select strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
...> from datetest;
strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
---
03
sqlite> select abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
...> from datetest;
abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
Well, I might as well use the substr() function, then:
sqlite> select abs(substr('2005-03-17T16:21:30', 6, 2));
abs(substr('2005-03-17T16:21:30', 6, 2))
3
Thanks for the implicit hint about using abs() for casting '03' to 3,
though.
Regards,
David
Re: [sqlite] Should Unary + Cast to a Number?
Sorry, I misunderstood the context.
sqlite> select * from datetest;
k d
-- ---
1 2005-03-17T16:21:30
sqlite> select strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
...> from datetest;
strftime("%m", substr(d,1,10)||' '||substr(d,-8,8))
---
03
sqlite> select abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
...> from datetest;
abs(strftime("%m", substr(d,1,10)||' '||substr(d,-8,8)))
3
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:21 PM, Matt Sergeant wrote: You can force the binding type in DBD::SQLite - see the DBI docs (grep for ":sql_types"). True, but in our application, that would require a lot more book-keeping than we'd like. Adding "+0" seems to do the cast we need to keep up with how DBD::SQLite casts '03'. Thanks, David
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:24 PM, Kurt Welgehausen wrote:
Yes, I know it supports it without the "T" ...
sqlite> select strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime');
strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime')
-
2005-03-17T16:21:30
No:
sqlite> select strftime("%m", '2005-03-17T16:21:30');
strftime("%m", '2005-03-17T16:21:30')
-
You see, I'm storing records with the T in the date string, but
SQLite's strftime() can't parse the date string with the T.
Regards,
David
Re: [sqlite] Should Unary + Cast to a Number?
> Yes, I know it supports it without the "T" ...
sqlite> select strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime');
strftime("%Y-%m-%dT%H:%M:%S", 'now', 'localtime')
-
2005-03-17T16:21:30
Re: [sqlite] Should Unary + Cast to a Number?
On 17 Mar 2005, at 15:13, David Wheeler wrote:
Probably off-topic for a SQLite list :-)
I'm not sure Perl will cast a non-numeric string
("2005-03-22T00:00:00") to a number. What number are you
looking for?
Actually the code that was cast was "substr(a, 6, 2)", which was
evaluating to "03", and I wanted it to be just 3, because when I bind
"03" in DBD::SQLite, it changes it to 3.
You can force the binding type in DBD::SQLite - see the DBI docs (grep
for ":sql_types").
Matt.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 1:23 PM, Kurt Welgehausen wrote: strftime doesn't support the ISO-8601 format ... I does if you give it the correct format string. Yes, I know it supports it without the "T", but ISO-8601 mandates the presence of the T. Regards, David
Re: [sqlite] Should Unary + Cast to a Number?
> strftime doesn't support the ISO-8601 format ... I does if you give it the correct format string. Regards
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 11:40 AM, Clark Christensen wrote:
Probably off-topic for a SQLite list :-)
I'm not sure Perl will cast a non-numeric string
("2005-03-22T00:00:00") to a number. What number are you
looking for?
Actually the code that was cast was "substr(a, 6, 2)", which was
evaluating to "03", and I wanted it to be just 3, because when I bind
"03" in DBD::SQLite, it changes it to 3.
If you were looking for 20050322, SQLite can do that for
you, too. Have a look at
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
I wasn't looking for 20050322, but I'm thrilled to have the pointers to
SQLite's date/time functions. I should be able to change "substr(a, 6,
2)" to "strftime("%m", a)" and then not have to cast.
...hrm, nope. "strftime("%m", a)" still produces "03" instead of just
"3". So I have to do "strftime("%m", a)+0". And strftime doesn't
support the ISO-8601 format "-MM-DDTHH:MM:SS" (note the "T") :-(
But aside from that, I'd still like to see unary + be able to cast '03'
to 3.
Thanks,
David
Re: [sqlite] Should Unary + Cast to a Number?
--- David Wheeler <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> Given the below script (using DBD::SQLite 1.08, which
> uses SQLite
> 3.1.3), the output is just:
>
>+0 Cast: 2005-03-22T00:00:00
>
> I'm wondering, however, if unary + shouldn't also be able
> to cast an
> expression to a number...shouldn't it?
>
> Thanks,
>
> David
>
> #!/usr/bin/perl -w
>
> use strict;
> use DBI;
>
> use constant SQLITE_FILE => shift;
>
> my $dbh = DBI->connect_cached(
> 'dbi:SQLite:dbname=' . SQLITE_FILE, '', '', {
> RaiseError => 1,
> PrintError => 0,
> }
> );
>
> END {
> $dbh->disconnect;
> $dbh->rollback;
> }
>
> $dbh->begin_work;
> $dbh->do("CREATE TABLE foo (a TEXT)");
> $dbh->do("INSERT INTO foo
> VALUES('2005-03-22T00:00:00')");
> my $sth = $dbh->prepare("SELECT * FROM foo WHERE
> (substr(a, 6, 2) =
> ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "No Cast: $row->[0]\n";
> }
>
> $sth = $dbh->prepare("SELECT * FROM foo WHERE (+substr(a,
> 6, 2) = ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "Unary Cast: $row->[0]\n";
> }
>
> $sth = $dbh->prepare("SELECT * FROM foo WHERE (substr(a,
> 6, 2)+0 = ?)");
> $sth->execute('03');
> while (my $row = $sth->fetchrow_arrayref) {
> print "+0 Cast: $row->[0]\n";
> }
Probably off-topic for a SQLite list :-)
I'm not sure Perl will cast a non-numeric string
("2005-03-22T00:00:00") to a number. What number are you
looking for? SQLite can give you an offset from the Unix
epoch from a properly formatted date-time string, but I
don't think you could get that using "select * from ...".
If you were looking for 20050322, SQLite can do that for
you, too. Have a look at
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
-Clark
[sqlite] Should Unary + Cast to a Number?
Hi All,
Given the below script (using DBD::SQLite 1.08, which uses SQLite
3.1.3), the output is just:
+0 Cast: 2005-03-22T00:00:00
I'm wondering, however, if unary + shouldn't also be able to cast an
expression to a number...shouldn't it?
Thanks,
David
#!/usr/bin/perl -w
use strict;
use DBI;
use constant SQLITE_FILE => shift;
my $dbh = DBI->connect_cached(
'dbi:SQLite:dbname=' . SQLITE_FILE, '', '', {
RaiseError => 1,
PrintError => 0,
}
);
END {
$dbh->disconnect;
$dbh->rollback;
}
$dbh->begin_work;
$dbh->do("CREATE TABLE foo (a TEXT)");
$dbh->do("INSERT INTO foo VALUES('2005-03-22T00:00:00')");
my $sth = $dbh->prepare("SELECT * FROM foo WHERE (substr(a, 6, 2) =
?)");
$sth->execute('03');
while (my $row = $sth->fetchrow_arrayref) {
print "No Cast: $row->[0]\n";
}
$sth = $dbh->prepare("SELECT * FROM foo WHERE (+substr(a, 6, 2) = ?)");
$sth->execute('03');
while (my $row = $sth->fetchrow_arrayref) {
print "Unary Cast: $row->[0]\n";
}
$sth = $dbh->prepare("SELECT * FROM foo WHERE (substr(a, 6, 2)+0 = ?)");
$sth->execute('03');
while (my $row = $sth->fetchrow_arrayref) {
print "+0 Cast: $row->[0]\n";
}
