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
** sqlite3RegisterDateTimeFunctions() found at the bottom of the file.
** All other code has file scope.
**
** $Id: date.c,v 1.76 2008/02/21 20:40:44 drh Exp $
**
** 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. 
**
** 1970-01-01 00:00:00 is JD 2440587.5

#define getValue sqlite3AtoF

/*
** Parse a timezone extension on the end of a date-time.
** The extension is of the form:
**
**        (+/-)HH:MM
**
** Or the "zulu" notation:
**
**        Z
**
** If the parse is successful, write the number of minutes
** of change in p->tz and return 0.  If a parser error occurs,
** return non-zero.
**
** A missing specifier is not considered an error.
*/
static int parseTimezone(const char *zDate, DateTime *p){
  int sgn = 0;
  int nHr, nMn;
  int c;
  while( isspace(*(u8*)zDate) ){ zDate++; }
  p->tz = 0;
  c = *zDate;
  if( c=='-' ){
    sgn = -1;
  }else if( c=='+' ){
    sgn = +1;
  }else if( c=='Z' || c=='z' ){
    zDate++;
    goto zulu_time;
  }else{
    return c!=0;
  }
  zDate++;
  if( getDigits(zDate, 2, 0, 14, ':', &nHr, 2, 0, 59, 0, &nMn)!=2 ){
    return 1;
  }
  zDate += 5;
  p->tz = sgn*(nMn + nHr*60);
zulu_time:
  while( isspace(*(u8*)zDate) ){ zDate++; }
  return *zDate!=0;
}

/*
** Parse times of the form HH:MM or HH:MM:SS or HH:MM:SS.FFFF.
** The HH, MM, and SS must each be exactly 2 digits.  The

# 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.25 2008/02/21 20:40:44 drh Exp $

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

# Skip this whole file if date and time functions are omitted
# at compile-time
#

set sqlite_current_time 0

datetest 5.1 {datetime('1994-04-16 14:00:00 +05:00')} {1994-04-16 09:00:00}
datetest 5.2 {datetime('1994-04-16 14:00:00 -05:15')} {1994-04-16 19:15:00}
datetest 5.3 {datetime('1994-04-16 05:00:00 +08:30')} {1994-04-15 20:30:00}
datetest 5.4 {datetime('1994-04-16 14:00:00 -11:55')} {1994-04-17 01:55:00}
datetest 5.5 {datetime('1994-04-16 14:00:00 -11:60')} NULL
datetest 5.6 {datetime('1994-04-16 14:00:00 -11:55  ')} {1994-04-17 01:55:00}
datetest 5.7 {datetime('1994-04-16 14:00:00 -11:55 x')} NULL
datetest 5.8 {datetime('1994-04-16T14:00:00Z')} {1994-04-16 14:00:00}
datetest 5.9 {datetime('1994-04-16 14:00:00z')} {1994-04-16 14:00:00}
datetest 5.10 {datetime('1994-04-16 14:00:00 Z')} {1994-04-16 14:00:00}
datetest 5.11 {datetime('1994-04-16 14:00:00z    ')} {1994-04-16 14:00:00}
datetest 5.12 {datetime('1994-04-16 14:00:00     z    ')} {1994-04-16 14:00:00}
datetest 5.13 {datetime('1994-04-16 14:00:00Zulu')} NULL
datetest 5.14 {datetime('1994-04-16 14:00:00Z +05:00')} NULL
datetest 5.15 {datetime('1994-04-16 14:00:00 +05:00 Z')} NULL

# localtime->utc and utc->localtime conversions.  These tests only work
# if the localtime is in the US Eastern Time (the time in Charlotte, NC
# and in New York.)
#
set tzoffset [db one {
  SELECT CAST(24*(julianday('2006-09-01') -