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 "YYYY-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, YYYY--mm-dd". > This means separate fileds, one for time and one for date. > If the format was: "YYYY-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] -----------------------------------------------------------------------------
