On Tuesday, June 19, 2012 10:08:27 PM UTC-7, Stefan Rohlfing wrote:
>
>
> Hi,
>
> I am trying to convert a SQL query (original source
> code<https://github.com/sausheong/tinyclone/blob/a17e49aa675f898f875b6cb07de11935b8dced26/tinyclone.rb#L129>)
>
> from Datamapper to Sequel.
>
> This is the query:
>
> SELECT date(created_at) as date, count(*) as count
> FROM visits
> WHERE link_short = '#{short}' and
> created_at between CURRENT_DATE-#{nunber_of_days} and
> CURRENT_DATE+1
> GROUP BY date(created_at)
>
Note that there is likely a trivial SQL injection vulnerability in that
code unless you are doing external validation of both the short and
number_of_days variables.
> And this is what I came up with:
>
> short = 'youtube'
> number_of_days = 15
>
> DB[:visits].group_and_count(:created_at).
> filter(:link_short => short).
> filter(:created_at => (Date.today - number_of_days) ..
> Date.today)short
>
> The resulting dataset is as follows:
>
> SELECT `created_at`, count(*) AS 'count'
> FROM `visits` WHERE ((`link_short` = 'youtube') AND
> (`created_at` >= '2012-06-05 12:52:27.595783+0800') AND (`created_at` <=
> '2012-06-20 12:52:27.595786+0800'))
> GROUP BY `created_at`
>
> This, however, does not work, as 'count' and 'group' should work on the
> date (year-month-day) and not the date *and* time.
>
>
I can't replicate that. With your code, I get (for short='youtube' and
number_of_days=10):
SELECT `created_at`, count(*) AS 'count' FROM `visits`
WHERE ((`link_short` = 'youtube') AND
(`created_at` >= '2012-06-09') AND (`created_at` <= '2012-06-19'))
GROUP BY `created_at`
I'm not sure why you are getting both the date and the time output. Could
you put together a self contained example that shows that SQL using an
SQLite memory database?
To get something closer to your original query, try the following dataset:
DB[:visits].group_and_count(:created_at).
filter(:link_short => short).
filter{(created_at >= (Sequel::CURRENT_DATE - number_of_days)) &
(created_at <= Sequel::CURRENT_DATE)}
This produces the following SQL on SQLite:
SELECT `created_at`, count(*) AS 'count' FROM `visits`
WHERE ((`link_short` = 'youtube') AND
(`created_at` >= (date(CURRENT_TIMESTAMP, 'localtime') - 10)) AND
(`created_at` <= date(CURRENT_TIMESTAMP, 'localtime')))
GROUP BY `created_at`
The main difference is the date(CURRENT_TIMESTAMP, 'localtime') instead of
CURRENT_DATE. This is done on SQLite as it more accurate than using
CURRENT_DATE. Not that it matters, because on SQLite, I'm not sure your
query does what you think:
$ sqlite3
SQLite version 3.7.9 --SOURCE-ID--
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT CURRENT_DATE+1;
2013
sqlite> SELECT CURRENT_DATE-1;
2011
SQLite has no date or time types (see http://sqlite.org/datatype3.html), so
CURRENT_DATE and CURRENT_TIMESTAMP are returned as strings, and doing a
mathematical operation on a string will convert the string to a number.
For your query to work correctly on SQLite, you'll need to use SQLite
specific date functions, see http://sqlite.org/lang_datefunc.html.
Something like:
DB[:visits].group_and_count(:created_at).
filter(:link_short => short).
filter{(created_at >= date(Sequel::CURRENT_DATE, "'-?
days'".lit(number_of_days.to_i))) & (created_at <=
date(Sequel::CURRENT_DATE,'+1 day'))}
Which produces the following SQL:
SELECT `created_at`, count(*) AS 'count' FROM `visits`
WHERE ((`link_short` = 'youtube') AND
(`created_at` >= date(date(CURRENT_TIMESTAMP, 'localtime'), '-10
days')) AND (`created_at` <= date(date(CURRENT_TIMESTAMP, 'localtime'), '+1
day')))
GROUP BY `created_at`
But then of course your code is not portable to other databases. Sequel
does not abstract date/time arithmatic enough to allow portable date/time
handling in many cases, at least, not yet.
I recommend that anyone that needs to do date/time handling in the database
use a database other than SQLite. Without native date/time types, Sequel
is a poor choice for that type of code.
Personally, I think it is a bad idea to use a different database in testing
than you use in production. Setup a local PostgreSQL installation. It's
fairly easy to do and will serve you much better in the long run.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/jcn_ybucFY8J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
