SQLite CVSTrac

*** 199,201 ****
--- 199,256 ----
  will not work right for dates that exist in the Julian calendar but which
  do not exist in the Gregorian calendar.
  Example: 1900-02-29.
+ 
+ ***
+ For my use I added new functions and functionalities to the date functions that
+ come with the sqlite 3.3.0 (can be used in older versions as well with small effort).
+ 
+ In main lines they are as follows:
+ 
+ 1: NNN days
+ 2: NNN hours
+ 3: NNN minutes
+ 4: NNN.NNNN seconds
+ 5: NNN months  (see #551 and [1163])
+ 6: NNN years  (see #551 and [1163])
+ 7: start of month
+ 8: start of year
+ 9: start of week  (!!! implemented)
+ 10: start of day
+ 11: weekday N  (see #551 and [1163])
+ 12: unixepoch
+ 13: localtime
+ 14: utc
+ 15: julian  (not implemented as of 2004-01-05)
+ 16: gregorian  (not implemented as of 2004-01-05)
+ 17: start of minute
+ 18: start of hour
+ 19: end of minute
+ 20: end of hour
+ 21: end of day
+ 22: end of week
+ 23: end of month
+ 24: end of year
+ 25: group seconds by
+ 26: group minutes by
+ 27: group hours by
+ 28: group days by
+ 29: group weeks by
+ 30: group months by
+ 31: group years by
+ 
+ The "start of" modifiers (7 through 10 and 17 through 18) shift the date backwards to the beginning of the current minute, hour, week, month, year or day.
+ 
+ The "end of" modifiers (19 through 24) shift the date forwards to
+ the end of the current minute, hour, week, month, year or day.
+ 
+ The "group * by" modifiers (25 through 31) round the date to the closest backward multiple supplied, with some limitations, to the current seconds (1 through 30), minutes (1 through 30), hours (1 through 12), days (1 through 15), weeks (1 through 26), months (1 through 6), years (1 through 100), these limitations are due to dont complicate the calculations when a multiple can span beyound the unit modified.
+ 
+ Ex:
+ 
+ SELECT datetime('2006-02-04 20:09:23','group hours by 3'); => '2006-02-04 18:00:00'
+ 
+ SELECT datetime('2006-02-05 20:09:23','group days by 3'); => '2006-02-04 00:00:00'
+ 
+ New functions "week_number(date)" returns the week number of the year on the supplied date parameter, "datetime2seconds(datetime)" return the number of seconds from the supplied datetime parameter.
+ 
+ The diff file ready to be applied to the file "date.c" in the src directory of sqlite 3.3.0 is at http://dad-it.com:8080/date.c.diff, I hope it's considered valuable to be merged in the official distribution.

sqlite - Date And Time Functions