SQLite

** This file contains the C functions that implement date and time
** functions for SQLite.  
**
** There is only one exported symbol in this file - the function
** sqliteRegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.4 2003/12/23 16:34:13 drh Exp $
**
** NOTES:
**
** SQLite processes all times and dates as Julian Day numbers.  The
** dates and times are stored as the number of days since noon
** in Greenwich on November 24, 4714 B.C. according to the Gregorian
** calendar system.

** argv[1] and following are modifiers.  Parse them all and write
** the resulting time into the DateTime structure p.  Return 0
** on success and 1 if there are any errors.
*/
static int isDate(int argc, const char **argv, DateTime *p){
  int i;
  if( argc==0 ) return 1;
  if( argv[0]==0 || parseDateOrTime(argv[0], p) ) return 1;
  for(i=1; i<argc; i++){
    if( argv[i]==0 || parseModifier(argv[i], p) ) return 1;
  }
  return 0;
}


/*
** The following routines implement the various date and time functions

*/
static void strftimeFunc(sqlite_func *context, int argc, const char **argv){
  DateTime x;
  int n, i, j;
  char *z;
  const char *zFmt = argv[0];
  char zBuf[100];
  if( argv[0]==0 || isDate(argc-1, argv+1, &x) ) return;
  for(i=0, n=1; zFmt[i]; i++, n++){
    if( zFmt[i]=='%' ){
      switch( zFmt[i+1] ){
        case 'd':
        case 'H':
        case 'm':
        case 'M':

     int nArg;
     int dataType;
     void (*xFunc)(sqlite_func*,int,const char**);
  } aFuncs[] = {
#ifndef SQLITE_OMIT_DATETIME_FUNCS
    { "julianday", -1, SQLITE_NUMERIC, juliandayFunc   },
    { "date",      -1, SQLITE_TEXT,    dateFunc        },
    { "time",      -1, SQLITE_TEXT,    timeFunc        },
    { "datetime",  -1, SQLITE_TEXT,    datetimeFunc    },
    { "strftime",  -1, SQLITE_TEXT,    strftimeFunc    },
#endif
  };
  int i;

  for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){

# 2003 October 31
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing date and time functions.
#
# $Id: date.test,v 1.3 2003/12/23 16:34:13 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

proc datetest {tnum expr result} {
  do_test date-$tnum [subst {
    execsql "SELECT coalesce($expr,'NULL')"

  datetest 6.13 {datetime('2000-07-01 12:00:00','localtime')} \
      {2000-07-01 08:00:00}
  datetest 6.14 {datetime('1969-07-01 12:00:00','localtime')} \
      {1969-07-01 07:00:00}
  datetest 6.15 {datetime('2039-07-01 12:00:00','localtime')} \
      {2039-07-01 07:00:00}
}

# Date-time functions that contain NULL arguments return a NULL
# result.
#
datetest 7.1 {datetime(null)} NULL
datetest 7.2 {datetime('now',null)} NULL
datetest 7.3 {datetime('now','localtime',null)} NULL
datetest 7.4 {time(null)} NULL
datetest 7.5 {time('now',null)} NULL
datetest 7.6 {time('now','localtime',null)} NULL
datetest 7.7 {date(null)} NULL
datetest 7.8 {date('now',null)} NULL
datetest 7.9 {date('now','localtime',null)} NULL
datetest 7.10 {julianday(null)} NULL
datetest 7.11 {julianday('now',null)} NULL
datetest 7.12 {julianday('now','localtime',null)} NULL
datetest 7.13 {strftime(null,'now')} NULL
datetest 7.14 {strftime('%s',null)} NULL
datetest 7.15 {strftime('%s','now',null)} NULL
datetest 7.16 {strftime('%s','now','localtime',null)} NULL

finish_test