SQLite Forum

Here is a better version that should work on both Linux (POSIX) and Windows and has two functions UnixStamp and JulianStamp which return the Unix Epoch Timestamp and Julian Day Epoch Timestamp.  You can enable correct rounding if you wish, otherwise the results will be returned to the maximum precision supported.

```
#ifdef _WIN32
#include <windows.h>
#else
#include <time.h>
/* math.h is required is we want to round results to the millisecond */
#if 0
#include <math.h>
#endif
#endif
// #include <math.h>
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1

/*
** UnixStamp function returns a floating point unix epoch time.
** JulianStamp function returns a floating point julian date.
**
** It calls the Windows PreciseTime API directly and therefore
** has a theoretical precision of 100 nanoseconds for the source
** time reduced and bounded by the IEEE floating point conversion.
**
** On non-Windows it uses the POSIX clock_gettime call and therefore
** has a theretical precision of 1 nanosecond, or as provided by the
** underlying Operating System, for the source time reduced and
** bounded by the IEEE floating point conversion.
**
** This function will attempt to preserve its value across invocations
** by attaching the value as auxdata to the single required parameter
** that should be a statement compile time constant, although there
** is no way to ensure that this requirement is being met.
**
** Using this function improperly may result in the heat death
** of the multiverse.  Consider yourself forewarned.
*/

static void _TimeStamp(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    sqlite_int64 huns = 0;
    double *timestamp = sqlite3_get_auxdata(context, 0);

    if (!timestamp)
    {
        timestamp = sqlite3_malloc(sizeof(double));
        if (!timestamp)
        {
            sqlite3_result_error_nomem(context);
            return;
        }
#ifdef _WIN32
        GetSystemTimePreciseAsFileTime((void*)&huns);
        *timestamp =  ((double)(huns  - 116444736000000000ll)) / 1.0e7;
#else
        {
            struct timespec t;

            if (clock_gettime(CLOCK_REALTIME, &t) == -1)
            {
                sqlite3_result_error(context, "Cannot get time", -1);
                return;
            }
            huns = 1;
            *timestamp = (double)t.tv_sec + (double)t.tv_nsec / 1.0e9;
        }
#endif
        /* Optional to correctly round the result to the millisecond */
        #if 0
        *timestamp *= 1000.0;
        *timestamp -= remainder(*timestamp, 1.0);
        *timestamp /= 1000.0;
        #endif
        if ((intptr_t)sqlite3_user_data(context))
        {
            *timestamp = *timestamp / 86400.0 + 2440587.5;
        }
    }
    sqlite3_result_double(context, *timestamp);
    if (huns)
    {
        sqlite3_set_auxdata(context, 0, timestamp, sqlite3_free);
    }
}


#ifdef _WIN32
#ifndef SQLITE_CORE
__declspec(dllexport)
#endif
#endif
int sqlite3_timestamp_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi)
{
    int nErr = 0;
    SQLITE_EXTENSION_INIT2(pApi);

    nErr += sqlite3_create_function(db, "UnixStamp",   1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, (void*)0, _TimeStamp, 0, 0);
    nErr += sqlite3_create_function(db, "JulianStamp", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, (void*)1, _TimeStamp, 0, 0);

    return nErr ? SQLITE_ERROR : SQLITE_OK;
}
```