SQLite sorting of DateTime values..... how? (1 Viewer)

waeberd

Portal Pro
August 16, 2004
314
1
Fribourg (CH)
Hi,

one small question:

How can I convert a C# DateTime into a number?

background:
I would like to store a date time into a SQLite table. Works fine, but I use a TEXT field.
=> Problem: SQL-sorting by this field is not possible!

=> idea: I'd like to store the date into a NUMBER field, so that a SQL-sort is possible (with an index on the field to boost it up).

Any ideas? Alternative workarounds?

Thanks!

Daniel
 

ojo

Portal Member
September 19, 2004
47
1
Ulstrup, Denmark
waeberd said:
Hi,

I would like to store a date time into a SQLite table. Works fine, but I use a TEXT field.
=> Problem: SQL-sorting by this field is not possible!

What about this solution:

Using string but converting to yyyyMMdd format.

Due to the year, then month, then day and the zero padding you will be able to sort in SQL. It's even human readable.....

Code:
string sDate = String.Format("{0:yyyyMMdd}",DateTime.Now);
and the other way around

Code:
DateTime dDate = DateTime.ParseExact("20041231","yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
This should handle your problem. By converting to C# DateTime before doing any date-time calculations this should work like a charm.

What do you think ?

;-) Ojo
 

waeberd

Portal Pro
August 16, 2004
314
1
Fribourg (CH)
Thanks ojo,

That's exactly what I thought too and your solution definitely works fine!

In the meantime I've found out that in-memory sorting works cool in my case, so I will stick to that.... if it gets too slow I will do a database-SQL sort!

The other problem with SQLite sorting is that SQLite simply does an strict ASCII sort (so items beginning with lowercase 'a' appear after items with uppercase 'Z', like this: "ABBA", "YES", "ZAPPA", "abba2"... )

Anybody has an idea of how to influence the SQLite sort order?
Workaround would be to have an uppercase version of the sort field stored in the table (to make sure the index can be used).

This seems too complicated to me.... so I'll stick to in-memory sorting for now!

Thanks again,

Daniel
 
A

Anonymous

Guest
waeberd said:
The other problem with SQLite sorting is that SQLite simply does an strict ASCII sort (so items beginning with lowercase 'a' appear after items with uppercase 'Z', like this: "ABBA", "YES", "ZAPPA", "abba2"... )
Code:
SELECT x FROM y ORDER BY x COLLATE NOCASE
Then add ASC or DESC as needed.
 

waeberd

Portal Pro
August 16, 2004
314
1
Fribourg (CH)
hmm... doesn't seem work:

Code:
sqlite> select title from application order by title;
Genesis
MAME
SNES
mame dirbrowse
my programs
sqlite> select title from application order by title collate nocase;
Genesis
MAME
SNES
mame dirbrowse
my programs
sqlite> select title from application order by title collate nocase asc;
Genesis
MAME
SNES
mame dirbrowse
my programs

Do I need to create the table column with some collate parameters?

thanks,

Daniel
 
A

Anonymous

Guest
waeberd said:
Hi,

one small question:

How can I convert a C# DateTime into a number?

background:
I would like to store a date time into a SQLite table. Works fine, but I use a TEXT field.
=> Problem: SQL-sorting by this field is not possible!

=> idea: I'd like to store the date into a NUMBER field, so that a SQL-sort is possible (with an index on the field to boost it up).

Any ideas? Alternative workarounds?

Thanks!

Daniel

Hi waeberd,

I see you already have a solution to the datetime problem but here are some alternatives.

I use the Ticks property on DateTime object to retrieve the amount of millisecs since some early date. Ticks can be stored in a long and is very convenient for comparasing.
To convert Ticks back to DateTime simply pass it as a parameter to the constructor.

Code:
long millisecs = aTime.Ticks; 
DateTime bTime = new DateTime(millisecs);

In utils of MP there are also some functions to convert DateTime to/from long. This format is used in the TVdatabase and may be considered MP standard i guess.

/ Fred
 

waeberd

Portal Pro
August 16, 2004
314
1
Fribourg (CH)
fred said:
Code:
long millisecs = aTime.Ticks; 
DateTime bTime = new DateTime(millisecs);

In utils of MP there are also some functions to convert DateTime to/from long. This format is used in the TVdatabase and may be considered MP standard i guess.

... and this is of course more efficient than string conversion!
Thanks! I will probably convert to this format in the next database version!

Regards,

Daniel
 
A

Anonymous

Guest
waeberd said:
Do I need to create the table column with some collate parameters?
Well I'll be dammned if you're not right. It doesn't work.

The manual explicitly states:
An ORDER BY clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation explicitly. In this case the explicit collation sequence is always used. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the default collation type of the column is used to determine sort order. If the expression is not a column, then the BINARY collation sequence is used.

I've even tried creating the column with a COLLATE NOCASE or REVERSE and it doesn't have any effect on the sort order. WTF? I know I've done this before and it has worked. Is our expression not a column? Clearly it is.
 

Users who are viewing this thread

Top Bottom