Skip to main content

ocaml sqlite3 date

Use case

I have a SQLite3 database, with Date, Integers and Float fields. I use it to make a poor-man accountig of my ppp0 interface traffic. Data I want to play with are in the format:

 $ sqlite3 accouting-copy "SELECT * FROM ifconfig" 1|2011-08-12 09:37:47|0.0       |0.0 2|2011-08-12 09:43:01|18629153.0|8124895.0 3|2011-08-12 09:43:03|18636044.0|8125679.0 4|2011-08-12 09:44:32|18694283.0|8159197.0 5|2011-08-12 09:50:01|19203494.0|8270963.0 6|2011-08-12 09:55:01|19265098.0|8311962.0
Where:
  • Field #1: integer, incremental
  • Field #2: date, SQLite "DATETIME('NOW')"
  • Field #3 and #4: Float, "RX" and "TX" counters parsed from "ifconfig ppp0". Sometimes, these counters reset: If the modem hangs and I must perform a "ifdown ppp0; ifup ppp0". That is why I need computation.

I want to account the traffic:

  • Between to Dates: To know how much I had for a given month
  • From a given Date to Now(): To approximately know my remaining traffic

I am not going to care about to computing logic in this article, but focus on data extraction.

Preparing the toplevel

In order to use these examples, those modules are needed:

 #use "topfind";; #require "calendar";; #require "sqlite3";;

Auxilliary functions and variables

Some variables:

 let db = Sqlite3.db_open "/home/mihamina/accouting-copy";; let the_query = "SELECT * FROM ifconfig";;

I need a couple of auxilliary functions.

To convert a "String Option" (Some "foo") to a String:

 let so_to_strig the_so = match the_so with | Some s -> s; | _ -> "";;

To get a Calendar Date from a an SQLite Date String:

 let date_from_sql  = CalendarLib.Printer.Precise_Fcalendar.from_fstring "%F %T" ;;

To print a Date:

 let date_to_string = CalendarLib.Printer.Precise_Fcalendar.to_string ;;

To add tso days to a Date (For testing or demonstration prupose):

 let add_2_days a_date = CalendarLib.Fcalendar.Precise.add a_date (CalendarLib.Fcalendar.Precise.Period.make 0 0 2 0 0 0.);;

The callback just to print the table content:

 let the_print_callback row headers = (Array.iter (fun s -> Printf.printf "  %-12s"              s)  headers); (print_endline ""); (Array.iter (fun s -> Printf.printf "  %-12s" (so_to_strig s)) row); (print_endline "");;
Thanks and Acknowledgments

The callback to play with the data, which is the most important for the work:

 let the_data_callback row headers= (* row.(0) row.(1)  row.(2)  row.(3)  *) (* id      date     rx       tx       *) (* int     Date     float    float    *) (* "headers" is no used at the moment *) let the_id = int_of_string   (so_to_strig (row.(0))) and the_rx = float_of_string (so_to_strig (row.(2))) and the_tx = float_of_string (so_to_strig (row.(3))) and the_date = CalendarLib.Printer.Precise_Fcalendar.from_fstring "%F %T" (so_to_strig (row.(1))) in print_string "the_id: "  ; print_int   the_id                    ; print_string "\t\t the_id doubled: "        ; print_int    (the_id * 2)                          ; print_string " "; print_string "the_tx: "  ; print_float the_tx                    ; print_string "\t\t the_tx doubled: "        ; print_float  (the_tx *. 2.)                        ; print_string " "; print_string "the_rx: "  ; print_float the_rx                    ; print_string "\t\t the_rx doubled: "        ; print_float  (the_rx *. 2.)                        ; print_string " "; print_string "the_date: "; print_string (date_to_string the_date); print_string "\t\t twodays after the_date: "; print_string (date_to_string (add_2_days the_date)); print_string " "; print_endline "=================================================================================================================="; ;;

Launching

To print all the table content:

 let result = Sqlite3.exec db ~cb:the_print_callback the_query;;

To make sample operations with the data:

  • double the ID and print it
  • double the RX and TX and print them
  • add 2 days to the Date and print it
 let result = Sqlite3.exec db ~cb:the_data_callback the_query;;

Sample Source code

The source code is on my Google Code Repository

Popular posts from this blog

Undefined global vim

Defining vim as global outside of Neovim When developing plugins for Neovim, particularly in Lua, developers often encounter the "Undefined global vim" warning. This warning can be a nuisance and disrupt the development workflow. However, there is a straightforward solution to this problem by configuring the Lua Language Server Protocol (LSP) to recognize 'vim' as a global variable. Getting "Undefined global vim" warning when developing Neovim plugin While developing Neovim plugins using Lua, the Lua language server might not recognize the 'vim' namespace by default. This leads to warnings about 'vim' being an undefined global variable. These warnings are not just annoying but can also clutter the development environment with unnecessary alerts, potentially hiding other important warnings or errors. Defining vim as global in Lua LSP configuration to get rid of the warning To resolve the "Undefined global vi...
Read more

LazyGit AI Commit Message

Having AI‑generated commit messages directly integrated into LazyGit If you use LazyGit every day, you already know how it turns Git from a chore into something you can actually enjoy. But there is one part of the workflow that still tends to feel a bit tedious: writing good commit messages. In this post, I show how to plug OpenAI models directly into LazyGit using a tiny one‑file BASH script, so you can get AI‑generated commit messages based on your actual diffs, without waiting for external tools to catch up with the new OpenAI Responses API . The result is a minimal, focused tool you can drop into your setup today: lgaicm . It behaves like a mini aichat that does exactly one thing: generate commit messages from Git diffs, optimized for LazyGit. Why AI‑generated commit messages in LazyGit? Commit messages matter. They are the stor...
Read more

CopilotChat GlobFile Configuration

CopilotChat GlobFile Configuration Want to feed multiple files into GitHub Copilot Chat from Neovim without listing each one manually? Let's add a tiny feature that does exactly that: a file glob that includes full file contents . In this post, we'll walk through what CopilotChat.nvim offers out of the box, why the missing piece matters, and how to implement a custom #file_glob:<pattern> function to include the contents of all files matching a glob. Using Copilot Chat with Neovim CopilotChat.nvim brings GitHub Copilot's chat right into your editing flow. No context switching, no browser hopping — just type your prompt in a Neovim buffer and let the AI help you refactor code, write tests, or explain tricky functions. You can open the chat (for example) with a command like :CopilotChat , then provide extra context using built-in functions. That “extra context” is where the magic really happens. Built-in functio...
Read more