Recent

Author Topic: SQLite Date and group by  (Read 11039 times)

ron

  • New member
  • *
  • Posts: 7
SQLite Date and group by
« on: January 20, 2008, 10:47:50 pm »
Hello,

I'm want to run a sql query like this one:

Code: [Select]
SELECT name, strftime('%m',Date1) as Month, avg(Scores) as Scores
from tblScores
where  strftime('%Y',Date1) = 2007
group by name, strftime('%m',Date1)
order by Month asc


This isn't working with lazarus, I think because the sqlite dataset stores the date as a string.

Does anyone know a way to use the group by in combination with date fields created by the sqlite dataset?

Ron.

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
RE: SQLite Date and group by
« Reply #1 on: January 21, 2008, 12:26:03 am »
The only way i see is to write a customized strftime function.
I'll put in my todo list

ron

  • New member
  • *
  • Posts: 7
RE: SQLite Date and group by
« Reply #2 on: January 21, 2008, 07:12:02 pm »
Thanks,

I'll have to think of a work-around for now.

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
RE: SQLite Date and group by
« Reply #3 on: January 22, 2008, 03:10:58 am »
The good news is that i managed how to create custom functions.

The bad news is that i could not find documentation for strftime function under sqlite.
Where i can find the specifications for this function?

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
RE: SQLite Date and group by
« Reply #4 on: January 22, 2008, 06:57:28 pm »
I managed to create a strftime function that implements what i think it should be.
To use do the following:

add sqlite3 to your uses clause.
copy that function to your unit:


procedure StrFTime(context: PSqlite3_Context; argc: LongInt; argv: PPSqlite3_Value); cdecl;  
var
  Year, Month, Day: word;
  FormatStr: String;
begin
  if (sqlite3_value_type(argv[1]) <> SQLITE_INTEGER) and
    (sqlite3_value_type(argv[1]) <> SQLITE_FLOAT) then
  begin
    sqlite3_result_null(context);
    Exit;
  end;
  FormatStr := UpperCase(sqlite3_value_text(argv[0]));
  DecodeDate(sqlite3_value_double(argv[1]), Year, Month, Day);
  if FormatStr = '%Y' then
    sqlite3_result_int(context, Year)
  else    
    if FormatStr = '%M' then
      sqlite3_result_int(context, Month)
    else
      if FormatStr = '%D' then
        sqlite3_result_int(context, Day)
      else
        sqlite3_result_null(context);
end;

After the very first Open call add the following code:


sqlite3_create_function(DataSet.SqliteHandle, 'strftime', 2, sqlite_any, nil, @strftime, nil, nil);


Test and give me some feedback.

I'll built in in the next release

ron

  • New member
  • *
  • Posts: 7
RE: SQLite Date and group by
« Reply #5 on: March 26, 2008, 09:12:56 pm »
Luiz,

I'm sorry for my very late reaction,
I'm just a beginner with pascal programming and I didn't get your function to work. I'm stuck on the message statistics.pas(133,88) Error: Incompatible type for arg no. 6: Got "<procedure variable type of procedure(Pointer, LongInt, PPsqlite3_value) of object;CDecl>", expected "<procedure variable type of procedure(Pointer, LongInt, PPsqlite3_value);CDecl>"

Ron.