SQLite Forum

I've had to produce a workaround under Windows, to avoid calling strftime() at all, which I copy here, in case anyone else is affected by this bug before it
gets fixed (which I hope one day it will be!) - this function parses ISO-8601
timestamps into 64-bit numbers of microseconds :


/* timestamp_parse.h:
 *
 *  To workaround bug in SQLite's 'strftime', this provides a function to parse timestamps in the same way,
 *  so that we can avoid using strftime.
 *
 *  The function expects timestamps to be of the form:
 *
 *    YYYY('-'or'/')MM('-'or'/')DD('T'or' ')HH':'MM':'SS'.'FFFFFF('+'or'-')HH:MM
 *
 *  The largest timestamp that does not return an error on Windows is:
 *    "3001/01/19@07:59:59.999999+00:00"
 *  ( "3001/01/19@08:00:00+00:00" returns an error ).
 *
 * Jason Vas Dias <jason.vas.dias@gmail.com> March 2021
 */

#ifndef _TIMESTAMP_PARSE_H_
#define _TIMESTAMP_PARSE_H_

#ifndef _INC_TIME
# include <time.h>
#endif

typedef unsigned long long U64_t;

typedef unsigned int U32_t;

extern int atoi( const char *);

static
char _ts_buf_[64] = {0};

static inline
U64_t timestamp_to_utc_ms_since_1970 ( register const char *ts )
{ if ( ts == NULL )
    return 0;
  register const char
   *p = ts;
  register char
   *np = &_ts_buf_[0];
  register U64_t
    epoch_second = 0;
  struct tm
    tm = {0};
  U32_t n_ms=0;
  int *flds[7] =
    { &tm.tm_year, &tm.tm_mon, &tm.tm_mday
    , &tm.tm_hour, &tm.tm_min, &tm.tm_sec
    , &n_ms
    }
  , tz_hour=0, tz_min=0;
  register int
   **fld = &flds[0];
  for(; *p && (np < &_ts_buf_[64]) && (fld < &flds[7]); ++p )
  { if(!((*p >= '0') && (*p <= '9')))
    { *np = '\0';
      if ( (fld == &flds[6])
         &&(np > &_ts_buf_[6])
         )
      { np = &_ts_buf_[6];
        *np = '\0';
      }
      if( np > &_ts_buf_[0])
        **fld = atoi(_ts_buf_);
      np = &_ts_buf_[0];
      if ( (fld == &flds[6])                // we need to support case where there is NO fractional second field
         ||(*p == '+')                      // & detect beginning of timezone offset field
         ||((fld > &flds[2]) &&(*p == '-')) // '-' not valid separator for time fields!
         ||(*p == 'Z')
         )
        break;
      fld += 1;
    }else
    { *np = *p;
      np += 1;
    }
  }
  if ( tm.tm_year < 1900 )
    return 0;
  tm.tm_year -= 1900;
  if ( tm.tm_mon < 1)
    return 0;
  tm.tm_mon -= 1;
  if ( (tm.tm_mon  > 11)
     ||(tm.tm_mday > 31)
     ||(tm.tm_hour > 23)
     ||(tm.tm_min  > 59)
     ||(tm.tm_sec  > 59)
     )
    return 0;
  epoch_second = _mktime64( &tm );
  if ( (epoch_second >= 0xfffffffffff0be3b) || (GetLastError() != 0 ))
    return 0;
  register bool
   is_negative = false;
  flds[0] = &tz_hour;
  flds[1] = &tz_min;
  fld = &flds[0];
  switch (*p)
  {case '\0':
   case 'Z':
     return (epoch_second * 1000000) + n_ms;
   case '-':
     is_negative = true;
     // fall through:
   case '+':
     for(++p, np=&_ts_buf_[0]; (fld < &flds[2]); ++p)
     { if(!((*p >= '0') && (*p <= '9')))
       { *np = '\0';
         if( np > &_ts_buf_[0] )
           **fld = atoi(_ts_buf_);
         fld += 1;
         np = &_ts_buf_[0];
       }else
       { *np = *p;
         np += 1;
       }
       if( !*p )
         break;
     }
     if( is_negative )
       epoch_second += (tz_hour * 3600) + (tz_min * 60);
     else
       epoch_second -= (tz_hour * 3600) + (tz_min * 60);
     break;
    default:
     break;
  }
  return ((epoch_second * 1000000) + n_ms);
}

#endif