Re: find difference in dates/items in same column

You can add a calculated column to your SQL query

SELECT brand, date, weight, datediff(day, '2001/01/01', date) AS daysS
+inceMillemium
  FROM test2 
 ORDER BY brand, date ASC
[download]

And then use [daysSinceMillemium] column to calculate differences in days. Thus you use simple arithmetic instead of Time::Local.

Comment on Re: find difference in dates/items in same column Select or Download Code

Replies are listed 'Best First'.
Re^2: find difference in dates/items in same column by zerocred (Beadle) on Nov 14, 2008 at 10:08 UTC
Thanks, unfortunately its more complicated... The SELECT datediff() is fine for finding ages from a fixed date but not for finding deltas between dates in the same column. Datediff can be used in SQL but then it begins to look like the SQL example and takes ages to run on my 120,000 records with about ~10,000 brands.	[reply]
Re^3: find difference in dates/items in same column by ccn (Vicar) on Nov 14, 2008 at 10:44 UTC
I think there is no better way than you have choosen already. All that I can suggest is cosmetic improvements only.	[reply]
Re^4: find difference in dates/items in same column by Anonymous Monk on Nov 14, 2008 at 10:55 UTC
Thanks, now I see your meaning - that does indeed simplify the date difference code tremendously. However, its the delta between dates in the same column that are the challenge! Thanks for your pointers - I'm sure the code could benefit from plenty of cosmetic surgery !	[reply]