Thank you very much for your reply! Please find my comments below:
On Wednesday, June 20, 2012 3:06:58 PM UTC+8, Jeremy Evans wrote: > > 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? > You are right, testing again I get the same output as you. Here is a self contained example: https://gist.github.com/2958805 > > 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` > Unfortunately, this query only return an empty array using SQLite or PostgreSQL. I added the code to the gist mentioned above so that you can test it easily. > > 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` > This query does produce an output, but it is not the correct one. I also added this code to the gist. > > 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. > Right now I am just getting familiar with Sequel, going through the documentation and testing out most methods. But you are right, it's better to test everything on the production database. Therefore I switched to PostgreSQL, dropping all tables after each run of my little test program. Provided I did not make any mistakes in the test code, how could I solve the problem of grouping by date when using a database other than SQLite? > 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/-/pfFugMQMs14J. 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.
