[sqlite] Re: SPAM-LOW: RE: [sqlite] Format of csv file RE: [sqlite] date/time implementation question
I use the tilde '~' character a lot.
On Thu, 15 Mar 2007, Griggs, Donald wrote:
> Regarding: "What is the default separator?" [for using with .import in
> the command line utility]
>
> It is the vertical bar ("|", virgule, pipe character).
>
> By the way, I don't *think* that .import allows the separator to be
> quoted, does it?
> E.g., don't try to import
> 5,238,"Cohen, Jr.",Rafi
> expecting the "Cohen, Jr." to be considered a single field.
>
> For that reason, depending on your data, sometimes the pipe or tab makes
> a better separator.
>
>
>
> -Original Message-
> From: Rafi Cohen [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 15, 2007 12:42 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Format of csv file RE: [sqlite] date/time
> implementation question
>
> Dennis, without knowing that we are talking here about non-standard
> format of the csv file, my client, for whom I'm preparing this project
> decided to change the file extension to .txt in order not to confuse
> with the standard format of .csv.
> Anyway, the time for this project is beginning to be tight so I'll still
> try today to implement the sql approach, but if I fail, at least for
> this version, I'll proceed with the first approach.
> Now, I understand that .import accepts any kind of file, just under
> condition that each line represents a valid row of the table and the
> separator is either the default one or the one given explicitly by the
> .separator command.
> What is the default separator? I could not discover this by looking into
> the code.
> My idea is to read the file sent by the client within the C application,
> check what needs to be checked for validation and store the relevant
> parts of it formatted appropriately in another file and then import from
> this file into a table and proceed as you suggested yesterday.
> Do you see any problem with this approach or have anhy other suggestion?
> Concerning separator, I can add the .separator "," command into the
> script before .import, but I may also use the default, if I know what it
> is.
> I tried this manually in sqlite3 and I know that coma is not the default
> separator as .import failed.
> But after .separator "," .import succeeded and I could indeed select the
> rows from the table.
> I hope this signs a success for me to implement this approach in my C
> code as you suggested.
> Thanks, Rafi.
>
> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 14, 2007 11:11 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Format lf csv file RE: [sqlite] date/time
> implementation question
>
>
> Rafi Cohen wrote:
> > Hi Dennis, the first approach is clear now and I may proceed with it.
> >
> Good to hear.
> > The second approach is interesting and chalenging, but leaves some
> > issues to clarify and in case I find solutions to those issues I well
> > may adopt it. 1. format of csv file: I have no idea how this csv file
> > is created and which database engine is used. I do know that I receive
>
> > such a file once or twice a day.
> > While reading the file onto the list of structures, I also validate
> the
> > consistency of the data in the file.
> > Before the data lines (starting with "d,") there is a header line
> > "hdr,". This line contains the sequential number of the file, number
> of
> > records in the file and a checksum on a specific field of the data
> (say
> > coumn 2).
> > As I knew nothing up to now about .import, I wonder if there is a way
> to
> > include those checings in the second approach?
> >
> Firstly, this does not sound like a standard CSV format file (see
> http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm ).
>
> Secondly, your validation checks can not be added to the normal csv
> .import command using the sqlite shell, but they could be added to a
> customized copy of the import routine that you add to your own code. It
> might also be possible to convert some of the validation tests to SQL
> check constraints on the table columns as well.
> > 2. The deletion of the future dates is incorrect. On the contrary, in
> > the first approach, I re-examine the remaining structures each half a
> > minute until any of them becomes past date, then I process it just
> > like any other past date structures and then free it. In case a new
> > .csv file arrives, I add the new list of structures to the remaining
> > ones and continue to examine them every half a minute. I could do the
> > same with the sql3_exe
Re: [sqlite] Format lf csv file RE: [sqlite] date/time implementation question
Hi Rafi,
If it were mine to do, I would concentrate on getting the data into a table
where I can work with it using SQL.
It sounds like your best bet is to write some simple code to read through your
CSV, validate its consistency (ignore the dates), and insert it into a table.
Then use Dennis's temp table scenario to process the rows as appropriate.
Obviously, it doesn't necessarily need to be a temp table as in "create temp
table...". It could easily be a non-temp "scratch", or "pending" table that
doesn't go away when the connection closes. That way, you can close and come
back later to deal with the rows that crossed-over into past-date state.
-Clark
- Original Message
From: Rafi Cohen <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, March 14, 2007 1:21:21 PM
Subject: [sqlite] Format lf csv file RE: [sqlite] date/time implementation
question
Hi Dennis, the first approach is clear now and I may proceed with it.
The second approach is interesting and chalenging, but leaves some
issues to clarify and in case I find solutions to those issues I well
may adopt it.
1. format of csv file: I have no idea how this csv file is created and
which database engine is used. I do know that I receive such a file once
or twice a day.
While reading the file onto the list of structures, I also validate the
consistency of the data in the file.
Before the data lines (starting with "d,") there is a header line
"hdr,". This line contains the sequential number of the file, number of
records in the file and a checksum on a specific field of the data (say
coumn 2).
As I knew nothing up to now about .import, I wonder if there is a way to
include those checings in the second approach?
2. The deletion of the future dates is incorrect. On the contrary, in
the first approach, I re-examine the remaining structures each half a
minute until any of them becomes past date, then I process it just like
any other past date structures and then free it.
In case a new .csv file arrives, I add the new list of structures to the
remaining ones and continue to examine them every half a minute.
I could do the same with the sql3_exec statement in the second approach,
but I need another approach for the case of the future records.
I hope you have satisfying answers for those 2 issues and then I'll be
glad to proceed with the second approach.
Thanks, Rafi.
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 14, 2007 9:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question
Rafi Cohen wrote:
> Dennis, I really appreciate your patience and willingness to help.
> Unfortunately, this still did not bring me to the expected solution. I
> will give you a small algorithm of what I need to do and I'm sure
> after this you'll know how to assist me. 1. I read a .csv file into a
> linked list of structures. 2. I examine eacch structure one after the
> other: Compare the datetime filed of the structure aginst the current
> date. If bigger (future), I skup to the next structure.
> If smaller or equal, I check if a row with the same id field already
> exists in the table:
> Select * from tbl where id = id-in-struct.
> If no such row exists, I insert a row according to the structure's
> field, otherwise, based on another criteria I EITHER UPDATE THE row
with
> a new value on the second column or delete the row.
> 3. After this process, I free the structure from the linked list and
> move to the next structure.
> Because of the last section, I thought I need to make the date
> comparison in C, but I may be wrong here.
> However, if the comparison is made thru sqlite, how can I know if
indeed
> an insert, update or delete was processed so that I can free the
> structures?
> All the sql statements I use above are, of course, prepared statements
> which I execute with wqlite3_step for the fields of each structure.
> I hope I'm clear, this time.
>
Rafi,
I think I have followed your description and I agree that you could do
this by iterating in C as you have described. If you want to do that and
all you need is an expedient way to get a date and time string for the
current time for the comparison in step 2 you have a couple of
approaches.
One simple way is to let sqlite do it for you by executing a single SQL
statement "select datetime('now')". This will return a ISO date and time
string that you can compare with the concatenated strings that are in
your structure. The other is to use the standard C library routines to
build an ISO formatted date and time string. These two examples are
shown below in a mix of C an pseudo code comments (you have to fill in
the blanks).
Get current date and time from sqlite:
sqlite3_stmt* get_now;
sqlite3_prepare(db, "select datetime'now'", -
Re: [sqlite] Format lf csv file RE: [sqlite] date/time implementation question
Rafi Cohen wrote:
Hi Dennis, the first approach is clear now and I may proceed with it.
Good to hear.
The second approach is interesting and chalenging, but leaves some
issues to clarify and in case I find solutions to those issues I well
may adopt it.
1. format of csv file: I have no idea how this csv file is created and
which database engine is used. I do know that I receive such a file once
or twice a day.
While reading the file onto the list of structures, I also validate the
consistency of the data in the file.
Before the data lines (starting with "d,") there is a header line
"hdr,". This line contains the sequential number of the file, number of
records in the file and a checksum on a specific field of the data (say
coumn 2).
As I knew nothing up to now about .import, I wonder if there is a way to
include those checings in the second approach?
Firstly, this does not sound like a standard CSV format file (see
http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm ).
Secondly, your validation checks can not be added to the normal csv
.import command using the sqlite shell, but they could be added to a
customized copy of the import routine that you add to your own code. It
might also be possible to convert some of the validation tests to SQL
check constraints on the table columns as well.
2. The deletion of the future dates is incorrect. On the contrary, in
the first approach, I re-examine the remaining structures each half a
minute until any of them becomes past date, then I process it just like
any other past date structures and then free it.
In case a new .csv file arrives, I add the new list of structures to the
remaining ones and continue to examine them every half a minute.
I could do the same with the sql3_exec statement in the second approach,
but I need another approach for the case of the future records.
I hope you have satisfying answers for those 2 issues and then I'll be
glad to proceed with the second approach.
In that case you could split the imported data into two tables using the
date test. And then process only the table that contains the old
records. The future records would remain in a second table. This second
table would probably be the same one you import your new csv file
records into. The processing of the old records would proceed as before.
//assumes table imported contains the imported records
//select records to process based on date and time
create table process as
select * from imported where (date || ' ' || time) <=
datetime('now');
delete from imported where id in (select id from process);
You could also skip the concatenation by splitting the data and time
test if you replace the condition above with
date <= date('now' and time <= time('now')
Its probably a case of six of one or half a dozen of the other.
There really are a lot of different possibilities for processing the
data once you have them in table in the database.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Format lf csv file RE: [sqlite] date/time implementation question
Hi Dennis, the first approach is clear now and I may proceed with it.
The second approach is interesting and chalenging, but leaves some
issues to clarify and in case I find solutions to those issues I well
may adopt it.
1. format of csv file: I have no idea how this csv file is created and
which database engine is used. I do know that I receive such a file once
or twice a day.
While reading the file onto the list of structures, I also validate the
consistency of the data in the file.
Before the data lines (starting with "d,") there is a header line
"hdr,". This line contains the sequential number of the file, number of
records in the file and a checksum on a specific field of the data (say
coumn 2).
As I knew nothing up to now about .import, I wonder if there is a way to
include those checings in the second approach?
2. The deletion of the future dates is incorrect. On the contrary, in
the first approach, I re-examine the remaining structures each half a
minute until any of them becomes past date, then I process it just like
any other past date structures and then free it.
In case a new .csv file arrives, I add the new list of structures to the
remaining ones and continue to examine them every half a minute.
I could do the same with the sql3_exec statement in the second approach,
but I need another approach for the case of the future records.
I hope you have satisfying answers for those 2 issues and then I'll be
glad to proceed with the second approach.
Thanks, Rafi.
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 14, 2007 9:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question
Rafi Cohen wrote:
> Dennis, I really appreciate your patience and willingness to help.
> Unfortunately, this still did not bring me to the expected solution. I
> will give you a small algorithm of what I need to do and I'm sure
> after this you'll know how to assist me. 1. I read a .csv file into a
> linked list of structures. 2. I examine eacch structure one after the
> other: Compare the datetime filed of the structure aginst the current
> date. If bigger (future), I skup to the next structure.
> If smaller or equal, I check if a row with the same id field already
> exists in the table:
> Select * from tbl where id = id-in-struct.
> If no such row exists, I insert a row according to the structure's
> field, otherwise, based on another criteria I EITHER UPDATE THE row
with
> a new value on the second column or delete the row.
> 3. After this process, I free the structure from the linked list and
> move to the next structure.
> Because of the last section, I thought I need to make the date
> comparison in C, but I may be wrong here.
> However, if the comparison is made thru sqlite, how can I know if
indeed
> an insert, update or delete was processed so that I can free the
> structures?
> All the sql statements I use above are, of course, prepared statements
> which I execute with wqlite3_step for the fields of each structure.
> I hope I'm clear, this time.
>
Rafi,
I think I have followed your description and I agree that you could do
this by iterating in C as you have described. If you want to do that and
all you need is an expedient way to get a date and time string for the
current time for the comparison in step 2 you have a couple of
approaches.
One simple way is to let sqlite do it for you by executing a single SQL
statement "select datetime('now')". This will return a ISO date and time
string that you can compare with the concatenated strings that are in
your structure. The other is to use the standard C library routines to
build an ISO formatted date and time string. These two examples are
shown below in a mix of C an pseudo code comments (you have to fill in
the blanks).
Get current date and time from sqlite:
sqlite3_stmt* get_now;
sqlite3_prepare(db, "select datetime'now'", -1, _now, NULL);
char now_datetime[20];
sqlite3_step(get_now);
strcpy(now_datetime, sqlite3_column_text(get_now, 0));
// read csv into list of structures
//for each structure in the list
char rec_datetime[20];
strcpy(rec_datetime, a_struct.date);
strcat(rec_datetime, " ");
strcat(rec_datetime, a_struct.time);
if (strcmp(rec_datetime, now_datetime) <= 0) {
//if row with matching id exists in table
//if record should be deleted
//delete record
//else
//update record based on structure
//else
//insert a new record into table
}
//free the structure
Get current date and time from C library:
char now_datetime[20];
time_t now = time(NULL);
struct tm *now_tm = localtime();
sprintf(now_datetime,
Re: [sqlite] date/time implementation question
Rafi Cohen wrote:
Dennis, I really appreciate your patience and willingness to help.
Unfortunately, this still did not bring me to the expected solution. I
will give you a small algorithm of what I need to do and I'm sure after
this you'll know how to assist me.
1. I read a .csv file into a linked list of structures.
2. I examine eacch structure one after the other:
Compare the datetime filed of the structure aginst the current date. If
bigger (future), I skup to the next structure.
If smaller or equal, I check if a row with the same id field already
exists in the table:
Select * from tbl where id = id-in-struct.
If no such row exists, I insert a row according to the structure's
field, otherwise, based on another criteria I EITHER UPDATE THE row with
a new value on the second column or delete the row.
3. After this process, I free the structure from the linked list and
move to the next structure.
Because of the last section, I thought I need to make the date
comparison in C, but I may be wrong here.
However, if the comparison is made thru sqlite, how can I know if indeed
an insert, update or delete was processed so that I can free the
structures?
All the sql statements I use above are, of course, prepared statements
which I execute with wqlite3_step for the fields of each structure.
I hope I'm clear, this time.
Rafi,
I think I have followed your description and I agree that you could do
this by iterating in C as you have described. If you want to do that and
all you need is an expedient way to get a date and time string for the
current time for the comparison in step 2 you have a couple of approaches.
One simple way is to let sqlite do it for you by executing a single SQL
statement "select datetime('now')". This will return a ISO date and time
string that you can compare with the concatenated strings that are in
your structure. The other is to use the standard C library routines to
build an ISO formatted date and time string. These two examples are
shown below in a mix of C an pseudo code comments (you have to fill in
the blanks).
Get current date and time from sqlite:
sqlite3_stmt* get_now;
sqlite3_prepare(db, "select datetime'now'", -1, _now, NULL);
char now_datetime[20];
sqlite3_step(get_now);
strcpy(now_datetime, sqlite3_column_text(get_now, 0));
// read csv into list of structures
//for each structure in the list
char rec_datetime[20];
strcpy(rec_datetime, a_struct.date);
strcat(rec_datetime, " ");
strcat(rec_datetime, a_struct.time);
if (strcmp(rec_datetime, now_datetime) <= 0) {
//if row with matching id exists in table
//if record should be deleted
//delete record
//else
//update record based on structure
//else
//insert a new record into table
}
//free the structure
Get current date and time from C library:
char now_datetime[20];
time_t now = time(NULL);
struct tm *now_tm = localtime();
sprintf(now_datetime, "%4d-%02d-%02d %02d:%02d:%02d",
now_tm->tm_year + 1900, now_tm->tm_mon + 1, now_tm->tm_mday,
now_tm->tm_hour, now_tm->tm_min, now_tm->tm_sec);
// read csv into list of structures
//for each structure in the list
char rec_datetime[20];
strcpy(rec_datetime, a_struct.date);
strcat(rec_datetime, " ");
strcat(rec_datetime, a_struct.time);
if (strcmp(rec_datetime, now_datetime) <= 0) {
//if row with matching id exists in table
//if record should be deleted
//delete record
//else
//update record based on structure
//else
//insert a new record into table
}
//free the structure
Both of these approaches use the fact that ISO format date and time
strings can be compared using a normal string comparison.
I think another approach might be worth considering though. Instead of
reading your csv data into a list of structures, import it into an
sqlite table. You could use the sqlite shell to do this, or you could
copy the code from the import routine in the sqlite shell directly into
your application (it's free open source code). With your csv records in
a temporary table you can do the manipulations in SQL.
// read csv into temporary table csv
system("sqlite3 mydb \"create temp table csv(...);.import myfile.csv
csv\"");
// delete the records with future dates (instead of skiping them)
delete from csv
where (date || ' ' || time) > datetime('now');
// split the csv table into new and existing records
create temp table new_csv as
select * from csv where id not in (select id from perm_table);
delete from csv
where id in (select id from new_csv);
// update the existing records in the permanent table
update perm_table
set field2
RE: [sqlite] date/time implementation question
Dennis, I really appreciate your patience and willingness to help.
Unfortunately, this still did not bring me to the expected solution. I
will give you a small algorithm of what I need to do and I'm sure after
this you'll know how to assist me.
1. I read a .csv file into a linked list of structures.
2. I examine eacch structure one after the other:
Compare the datetime filed of the structure aginst the current date. If
bigger (future), I skup to the next structure.
If smaller or equal, I check if a row with the same id field already
exists in the table:
Select * from tbl where id = id-in-struct.
If no such row exists, I insert a row according to the structure's
field, otherwise, based on another criteria I EITHER UPDATE THE row with
a new value on the second column or delete the row.
3. After this process, I free the structure from the linked list and
move to the next structure.
Because of the last section, I thought I need to make the date
comparison in C, but I may be wrong here.
However, if the comparison is made thru sqlite, how can I know if indeed
an insert, update or delete was processed so that I can free the
structures?
All the sql statements I use above are, of course, prepared statements
which I execute with wqlite3_step for the fields of each structure.
I hope I'm clear, this time.
Thanks, Rafi.
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 14, 2007 5:21 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question
Rafi Cohen wrote:
> Thanks Denis for your detailed explanation. My needs differ from the
> example you gave. I need to compare the date on each row with the
> current date, if the rowdate has passed then I either insert or update
> this row on a specific table, otherwise I leave this row for a future
> check. So, the comparison has to be made in C and not sqlite, I think.
> Now, suppose I brought the rowdate to the format "-mm--dd
HH:MM:SS".
> In order to call strcmp() to compare with the current date, I need to
> bring the current date to the same format. How can I do this, or you
> have other ideas to make this comparison?
>
Rafi,
There is no need for C in case you have given. A simple insert of
selected data should do.
insert or ignore into over_due
select id from schedule
where (due_date || ' ' || due_time) < datetime('now');
This uses one of sqlite's builtin date and time functions (see
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ) to format
the current time into an ISO format that can be compared directly with
the concatenated fields from your existing table. The id of any records
that meet the condition are inserted into the over_due table (or ignored
if they already exist in that table).
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.11/721 - Release Date:
3/13/2007 4:51 PM
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] date/time implementation question
Rafi Cohen wrote:
Thanks Denis for your detailed explanation. My needs differ from the
example you gave.
I need to compare the date on each row with the current date, if the
rowdate has passed then I either insert or update this row on a specific
table, otherwise I leave this row for a future check.
So, the comparison has to be made in C and not sqlite, I think.
Now, suppose I brought the rowdate to the format "-mm--dd HH:MM:SS".
In order to call strcmp() to compare with the current date, I need to
bring the current date to the same format. How can I do this, or you
have other ideas to make this comparison?
Rafi,
There is no need for C in case you have given. A simple insert of
selected data should do.
insert or ignore into over_due
select id from schedule
where (due_date || ' ' || due_time) < datetime('now');
This uses one of sqlite's builtin date and time functions (see
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ) to format
the current time into an ISO format that can be compared directly with
the concatenated fields from your existing table. The id of any records
that meet the condition are inserted into the over_due table (or ignored
if they already exist in that table).
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] date/time implementation question
Thanks Denis for your detailed explanation. My needs differ from the example you gave. I need to compare the date on each row with the current date, if the rowdate has passed then I either insert or update this row on a specific table, otherwise I leave this row for a future check. So, the comparison has to be made in C and not sqlite, I think. Now, suppose I brought the rowdate to the format "-mm--dd HH:MM:SS". In order to call strcmp() to compare with the current date, I need to bring the current date to the same format. How can I do this, or you have other ideas to make this comparison? Thanks, Rafi. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 13, 2007 7:15 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] date/time implementation question Rafi Cohen wrote: > Hi, I hope this question is not off-topic for this list. > I'm recieveing, occasionally, a .csv file from a source which I need > to process accordcing to some criteria and either insert, delet or > update it's rows into a database. One of it's criterias is date > comparison. In other words, proceeding differently when the date/time > mentioned on a specific row is a past date or future date comparing > with the current date/time. My problem is the format in which I > recieve the date and time: "HH:MM:SS, --mm-dd". > This means separate fileds, one for time and one for date. > If the format was: "-mm-dd HH:MM:SS", I could call strptime to stor > this in a struct tm, then cakk mktime to get this in a time_t varialbe > and then call difftime with the current date/time to make the comparison > (I'm using C as programmikng language on Linux). > So, my question is: should I unify the 2 strings into one to obtain the > second format and then proceed as I explained above, or is there a way > to compare the date with the current date separately and the time with > the current time separately in case the dates are equal? > If the conclusion will be to uify the strings into one, should I create > a table with a single date/time column or still keep the 2 fileds > separately in my sql table? > Thanks, Rafi. > > Rafi, It depends. :-) It depends on what you think is more important, execution speed, database size, simplicity of coding, etc... Assuming the date format you gave has a typo and there is really only one '-' between the year and month in your existing fields, you should be able to do your date comparisons in sqlite. Use concatenation to build a single date and time string. With your date and time in ISO format you can then use string comparisons as date comparisons select case when (date || ' ' || time) < ?limit_date then field_one else field_two end from my_table HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.10/720 - Release Date: 3/12/2007 7:19 PM - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] date/time implementation question
Rafi Cohen wrote: Hi, I hope this question is not off-topic for this list. I'm recieveing, occasionally, a .csv file from a source which I need to process accordcing to some criteria and either insert, delet or update it's rows into a database. One of it's criterias is date comparison. In other words, proceeding differently when the date/time mentioned on a specific row is a past date or future date comparing with the current date/time. My problem is the format in which I recieve the date and time: "HH:MM:SS, --mm-dd". This means separate fileds, one for time and one for date. If the format was: "-mm-dd HH:MM:SS", I could call strptime to stor this in a struct tm, then cakk mktime to get this in a time_t varialbe and then call difftime with the current date/time to make the comparison (I'm using C as programmikng language on Linux). So, my question is: should I unify the 2 strings into one to obtain the second format and then proceed as I explained above, or is there a way to compare the date with the current date separately and the time with the current time separately in case the dates are equal? If the conclusion will be to uify the strings into one, should I create a table with a single date/time column or still keep the 2 fileds separately in my sql table? Thanks, Rafi. Rafi, It depends. :-) It depends on what you think is more important, execution speed, database size, simplicity of coding, etc... Assuming the date format you gave has a typo and there is really only one '-' between the year and month in your existing fields, you should be able to do your date comparisons in sqlite. Use concatenation to build a single date and time string. With your date and time in ISO format you can then use string comparisons as date comparisons select case when (date || ' ' || time) < ?limit_date then field_one else field_two end from my_table HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] date/time implementation question
Hi, I hope this question is not off-topic for this list. I'm recieveing, occasionally, a .csv file from a source which I need to process accordcing to some criteria and either insert, delet or update it's rows into a database. One of it's criterias is date comparison. In other words, proceeding differently when the date/time mentioned on a specific row is a past date or future date comparing with the current date/time. My problem is the format in which I recieve the date and time: "HH:MM:SS, --mm-dd". This means separate fileds, one for time and one for date. If the format was: "-mm-dd HH:MM:SS", I could call strptime to stor this in a struct tm, then cakk mktime to get this in a time_t varialbe and then call difftime with the current date/time to make the comparison (I'm using C as programmikng language on Linux). So, my question is: should I unify the 2 strings into one to obtain the second format and then proceed as I explained above, or is there a way to compare the date with the current date separately and the time with the current time separately in case the dates are equal? If the conclusion will be to uify the strings into one, should I create a table with a single date/time column or still keep the 2 fileds separately in my sql table? Thanks, Rafi.
