U++ forum: Welcome to the forum

Search on this site

Search in forums

Home » Developing U++ » U++ Developers corner » Date emulation for Sqlite

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Date emulation for Sqlite [message #5291]

Tue, 12 September 2006 17:37

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

Sqlite does not support DATE type, which is a bit trouble with U++, as we cannot use natural Date <- column conversions.

I have this idea of Date emulation in Sqlite:

Store Date d as

(d - Date(1970, 1, 1)) * 1.0e300.

This way we would know that any double stored in database bigger than 1.0e300 is Date. Comparisons would work as well, the only downsize is that "number of days between" would be a bit high and that you would not be able to store doubles bigger than 1.0e300...

Should I do that? Any better solutions?

Mirek

Report message to a moderator

Re: Date emulation for Sqlite [message #5292 is a reply to message #5291]

Tue, 12 September 2006 18:20

Ulti
Messages: 108
Registered: September 2006

Experienced Member

maybe this can help,I don't match datetime type yet. Razz

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Report message to a moderator

Re: Date emulation for Sqlite [message #5293 is a reply to message #5292]

Tue, 12 September 2006 18:28

zsolt
Messages: 697
Registered: December 2005
Location: Budapest, Hungary

Contributor

My problem with this special double is that it would break two things:
1. Internal sqlite date operations as Ulti pointed to that
2. the readability of the result of a simple sql select.

I don't know well the logic of sch files yet, but wouldn't it be possible to use them to get the type of a column?

Report message to a moderator

Re: Date emulation for Sqlite [message #5294 is a reply to message #5291]

Tue, 12 September 2006 20:07

unodgs
Messages: 1366
Registered: November 2005
Location: Poland

Ultimate Contributor

The only problem with sqlite is when you select "stringed dates" into value and you want that value to be date type.
Now I use ScanDate which seems to work well.

Value dt = ScanDate(String(SQL[DT])); //DT column that holds date

Date ScanDate(String &s)
{
if(s.IsEmpty())
return Null;
int year = atoi(s.Left(4));
int month = atoi(s.Mid(5, 2));
int day = atoi(s.Right(2));

return Date(year, month, day);
}

But I agree that is troublesome and your chage is wanted.

[Updated on: Tue, 12 September 2006 22:24]

Report message to a moderator

Re: Date emulation for Sqlite [message #5295 is a reply to message #5293]

Tue, 12 September 2006 20:33

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

zsolt wrote on Tue, 12 September 2006 12:28

1. can be to large degree solved by SqlExp. In fact, it is "timestamp" type in disguise...

2. OK, this has not come to my mind, that is valid concern. OTOH, it affects non-U++ tools only. In U++, you would be able to get correct dates only.

What about to be a little crazy and encode Date(2006, 9, 12) as 0.20060912e300 ? Smile

Anyway, any other ideas how to store Date into Sqlite?

The only other one that I have is to store it as string with prefix - that however would mean to introduce some trouble to regular strings as well.

Well, I guess I owe the reason why that is so important. The thing to solve is that you cannot now with Sqlite associate EditDate with Sqlite column. Especially troublesome with SqlCtrls or SqlArray.

Mirek

Report message to a moderator

Re: Date emulation for Sqlite [message #5296 is a reply to message #5295]

Tue, 12 September 2006 20:57

zsolt
Messages: 697
Registered: December 2005
Location: Budapest, Hungary

Contributor

Quote:

What about to be a little crazy and encode Date(2006, 9, 12) as 0.20060912e300 ?

It is much more readale Smile

BTW, what about time?

Report message to a moderator

Re: Date emulation for Sqlite [message #5297 is a reply to message #5296]

Tue, 12 September 2006 21:01

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

Time would be nice as well, but is not nearly as important. I am using 2 DATE columns per table average, however I never used any time column in my career.

Mirek

Report message to a moderator

Re: Date emulation for Sqlite [message #5337 is a reply to message #5297]

Thu, 14 September 2006 11:02

zsolt
Messages: 697
Registered: December 2005
Location: Budapest, Hungary

Contributor

FYI: Using sqlite, it is possible, to use any typename in a create table statement. For example, this code is valid SQL:

create table test (id integer primary key, some_date_field date)

And of course, you can get this "custom" type, querying the type of the field.

Report message to a moderator

Re: Date emulation for Sqlite [message #5339 is a reply to message #5337]

Thu, 14 September 2006 11:26

zsolt
Messages: 697
Registered: December 2005
Location: Budapest, Hungary

Contributor

You can use these two functions to get the declared type of a column:

const char *sqlite3_column_decltype(sqlite3_stmt *, int i);
const void *sqlite3_column_decltype16(sqlite3_stmt*,int);

Report message to a moderator

Re: Date emulation for Sqlite [message #5347 is a reply to message #5339]

Fri, 15 September 2006 14:18

mirek
Messages: 13975
Registered: November 2005

Ultimate Member

Well, looks like an excellent option, unfortunately it seems like it does not work for

select max(mydate) from table

(or any other expression).

Report message to a moderator

Re: Date emulation for Sqlite [message #6762 is a reply to message #5295]

Sun, 26 November 2006 18:07

fabio
Messages: 9
Registered: November 2006

Promising Member

EditDate not work with SqlCtrl and Sqlite, but you can make new ctrl like SqlEditDate which work with date like "yyyy-mm-dd" in sqlite table. The only requirement is SQL.GetDialect().

You can change method for support more formats like yyyymmdd (string like number) and others.

//----------------------------------------------------------
//Add this class definition in SqlCtrl.h

class SqlEditDate : public EditDate
{
public:
virtual void SetData(const Value& data);
};

//--------------------------------------------------
// Add this method in SqlCtrl.cpp

void SqlEditDate::SetData(const Value& data)
{
Value valdata = data;
if (SQL.GetDialect()==SQLITE3){
switch(data.GetType()){
case STRING_V:
case WSTRING_V:
valdata = Date(atoi(data.ToString().Mid(0,4)),atoi(data.ToString().Mid (5,2)),atoi(data.ToString().Mid(8,2)));
break;
}
}
SetText((WString)convert->Format(valdata));
}
//-------------------------------------------------

Fabio

Attachment: SqlEditDate.txt
(Size: 0.67KB, Downloaded 676 times)

Report message to a moderator

Re: Date emulation for Sqlite [message #6763 is a reply to message #6762]

Sun, 26 November 2006 18:24

zsolt
Messages: 697
Registered: December 2005
Location: Budapest, Hungary

Contributor

Quote:

EditDate not work with SqlCtrl and Sqlite, but you can make new ctrl like SqlEditDate which work with date like "yyyy-mm-dd" in sqlite table.

This is very good idea.
But I think, the main problem is, that it doesn't resolve the Value problem. The Value class is typed internally, and the problem is, that in the core of SQL classes you can not set Value instances correctly, because SQLite doesn't report date types in metadata of query results.

Report message to a moderator

Previous Topic:	A new way how to create "plugin"
Next Topic:	uvs2 as "public application"

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Wed Apr 24 02:56:47 CEST 2024

Total time taken to generate the page: 0.01547 seconds