CODEWITHSUNDEEP
c++postgresqlquestdb
Brian Smith
Brian Smith

Reputation: 1315

How do I insert a timestamp in a prepared statement via postgres?

I'm sending records to a table in QuestDB and so far I have the following:

#include <libpq-fe.h>
#include <stdio.h>
#include <stdlib.h>
#include <sys/time.h>
#include <string.h>

void do_exit(PGconn* conn)
{
    PQfinish(conn);
    exit(1);
}
int main()
{
    PGconn* conn = PQconnectdb(
        "host=localhost user=admin password=quest port=8812 dbname=qdb");
    if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to database failed: %s\n",
            PQerrorMessage(conn));
        do_exit(conn);
    }
    // Simple query
    PGresult* res = PQexec(conn,
        "CREATE TABLE IF NOT EXISTS trades (ts TIMESTAMP, name STRING, value INT) timestamp(ts);");
    PQclear(res);

    int i;
    for (i = 0; i < 5; ++i) {
        char timestamp[30];
        char milis[7];
        struct timeval tv;
        time_t curtime;
        gettimeofday(&tv, NULL);
        strftime(timestamp, 30, "%Y-%m-%dT%H:%M:%S.", localtime(&tv.tv_sec));
        snprintf(milis, 7, "%d", tv.tv_usec);
        strcat(timestamp, milis);

        const char* values[1] = { timestamp };
        int lengths[1] = { strlen(timestamp) };
        int binary[1] = { 0 };

        res = PQexecParams(conn,
            "INSERT INTO trades VALUES (to_timestamp($1, 'yyyy-MM-ddTHH:mm:ss.SSSUUU'), 'timestamp', 123);",
            1, NULL, values, lengths, binary, 0);
    }
    res = PQexec(conn, "COMMIT");
    printf("Done\n");
    PQclear(res);
    do_exit(conn);
    return 0;
}

The problem with this is that I'm doing a lot of juggling around the string conversion and then eventually using to_timestamp(). I would like to get rid of this and directly insert the value during PQexecParams in a concise way.

The examples shown with PQexecParams in the Postgres docs don't give any guidance about how to use the timestamp or date type, as far as I can see.

Upvotes: 0

Views: 1159

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247765

You are doing it right.

Parameters to a prepared statement can either be sent in text format, like you are doing, or in binary format. For binary format you would have to set

int binary[1] = { 1 };

and convert the timestamp to the internal PostgreSQL format. In the case of timestamp or timestamp with time zone, that is an 8-byte integer (in network byte order) containing the number of microseconds since 2000-01-01 00:00:00.

I guess it would not be much simpler to convert the timestamp to that format, so you would not gain a lot, but end up with code that potentially depends on the architecture of the server machine.

Upvotes: 1

Related Questions