Date Time & Timestamps with SQLite

slayster

I've been working on a module on and off for years now. I recently got back into it and I've been updating to make things compatible with persistence. I was surprised when Googling how little information there seemed to be about dates, times & timestamps. I found plenty of threads of people asking the same questions I was asking myself: How do I stop a quest being repeated until 24 hours of real time have passed? How to I reactivate spawn triggers on a schedule? Etc. The most common suggestions seemed to be using heartbeats or pseudo-heartbeats to create a virtual clock. However I realized that with the recent addition of Sqlite to NWN:EE you can just use that now. I find it hard to believe I'm the only who's had that idea but I couldn't find any example code out there doing that. So I whipped up a quick include script for my own module and I figured I'd share it here for anyone else wondering the same in the future. Get datetime and get timestamp functions which can be stored in a variable for comparison later.

// Include for getting real world dates, times and timestamps using the
// SQL functions added in version 81.8193.15
// @author Brendan Ellis https://slayweb.com

// Get the current date/time
// @return string in the format YYYY-MM-DD HH:MM:SS, or an SQL error
string GetDateTimeString() {
    string strDateTime;
    sqlquery sqlDT = SqlPrepareQueryObject(GetModule(),"SELECT datetime('now')");
    if (SqlStep(sqlDT)) {
        strDateTime = SqlGetString(sqlDT,0);
    } else {
        strDateTime = SqlGetError(sqlDT);
    }
    return strDateTime;
}

// Get the current unix timestamp
// @return integer, the number of seconds since 1970. 0 on error.
int GetTimestamp() {
    int intTimestamp;
    sqlquery sqlTS = SqlPrepareQueryObject(GetModule(),"SELECT strftime('%s','now')");
    if (SqlStep(sqlTS)) {
        intTimestamp = SqlGetInt(sqlTS,0);
    } else {
        intTimestamp = 0;
    }
    return intTimestamp;
}

// Get the current date/time but transformed as specified for example
// "+1 day" or "+2 months" or "-1 year" etc.
// For the full list of modifiers see:
// https://www.sqlite.org/lang_datefunc.html#modifiers
// @param string $modifiers the modifer string as described in the SQLite docs
// @return string in the format YYYY-MM-DD HH:MM:SS, or an SQL error
string GetModifiedDateTimeString(string modifiers) {
    string strDateTime;
    sqlquery sqlDT = SqlPrepareQueryObject(GetModule(),"SELECT datetime('now',"+modifiers+")");
    if (SqlStep(sqlDT)) {
        strDateTime = SqlGetString(sqlDT,0);
    } else {
        strDateTime = SqlGetError(sqlDT);
    }
    return strDateTime;
}

// Get the current unix timestamp
// @return integer, the number of seconds since 1970.
int GetModifiedTimestamp(string modifiers) {
    int intTimestamp;
    string strDateTime;
    sqlquery sqlDT = SqlPrepareQueryObject(GetModule(),"SELECT datetime('now',"+modifiers+")");
    if (SqlStep(sqlDT)) {
        strDateTime = SqlGetString(sqlDT,0);
        sqlquery sqlTS = SqlPrepareQueryObject(GetModule(),"SELECT strftime('%s','"+strDateTime+"')");
        if (SqlStep(sqlTS)) {
            intTimestamp = SqlGetInt(sqlTS,0);
        } else {
            intTimestamp = 0;
        }
    } else {
        intTimestamp = 0;
    }
    return intTimestamp;

}

And a bit of example usage.

#include "inc_sqldatetime"

void main()
{

    // get the current date/time string
    SpeakString(GetDateTimeString());
    // get the current date/time string +1 day
    SpeakString(GetModifiedDateTimeString("'+1 day'"));

    // get the current unix timestamp
    SpeakString(IntToString(GetTimestamp()));
    // get the current unix timestamp +1 month -1 day
    SpeakString(IntToString(GetModifiedTimestamp("'+1 month','-1 day'")));

}

ForSerious

Props for throwing this together.
I've been doing something similar but with the game time system. I figured out that one game month is close enough to 24 hours that it was acceptable to me.

Though I really like what you've made here, it wouldn't work to just plug it in to my module because some parts rely on the date resetting when the server resets.

WilliamDraco

NWNX used to have functions to get real-time, and when sqlite was introduced that was replaced with a non-nwnx include library to do the same.

Still well done for figuring out a working solution yourself, but it's likely most are using the include in the nwnx repo.

Ɲ????Ʀ????

I also wanted to do this in script but as you said there is a lack of info related to this specific topic. It was on my backburner as its a minor nuance in the grand scheme of things. Publicly shared examples are also great to draw ideas from. Kudos to you!