SQLTime

sqltime.c

/*
 * World-time, by Hasan Diwan <hasandiwan+sqlTime@gmail.com>
 */

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

    static int
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    return 0;
}

int main(int argc, char **argv) {
    const char *conninfo = "dbname=template1 user=***** password=*************** host=hasan.d8u.us";
    char *token, *tz; 
    PGconn     *conn;
    PGresult   *res, *tzInfo;
    int        nFields, i;

    /*
     * If the user supplies a parameter on the command line, use it as the
     * conninfo string; otherwise default to setting dbname=postgres and using
     * environment variables or defaults for all other connection parameters.
     */

    /* Make a connection to the database */
    conn = PQconnectdb(conninfo);

    /* Check to see that the backend connection was successfully made */
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "%s\n", PQerrorMessage(conn));
        exit_nicely(conn);
    }

    if (argv[1] == NULL) {
        tzInfo = PQexec(conn, "select name as name, utc_offset as offset from pg_timezone_names order by name");
        if (PQntuples(tzInfo) > 1) {
            for (i = 0; i != PQntuples(tzInfo); i++) {
                if (strlen(PQgetvalue(tzInfo, i, 0)) > 4) {
                    printf("%s, %s\n", PQgetvalue(tzInfo, i, 0), PQgetvalue(tzInfo, i, 1));
                }
            }
        }
    } else {
        res = PQexecParams(conn, "SELECT now() at time zone $1", 1, NULL, &(argv[1]), NULL, NULL, 0);
        if (PQntuples(res) == 1) {
            printf("%s\n", PQgetvalue(res, 0, 0));
        } else {
            tzInfo = PQexec(conn, "select name from pg_timezone_names");
            for (i = 0; i != PQntuples(tzInfo); i++) {
                tz = PQgetvalue(tzInfo, i, 0);
                if(nFields == 0) {
                    if (strcasestr(tz, argv[1]) != NULL) {
                        res = PQexecParams(conn, "SELECT now() at time zone $1", 1, NULL, &tz, NULL, NULL, 0);
                        printf("%s:- %s\n", tz, PQgetvalue(res, 0, 0));
                    }
                } else {
                    fprintf(stderr, "timezone not found, I even tried substring search\n"); 
                }
            }
        }
    }
    return exit_nicely(conn);
}