Database Representation

Formatting Age

Sometimes it's useful to return a timestamp that is interpreted, or summarized in some way. The database may be a good place to do this. Some examples might be:

 1 mon 25 days ago
 1 year 1 day ago
 3 hours ago
 less then 1 hour ago

To implement this kind of logic

CREATE FUNCTION format_age(ts timestamp with time zone)
RETURNS text AS $$
  SELECT
    CASE
      WHEN now() - ts > interval '1 day'
      THEN date_trunc('day', now() - ts) || ' ago'
      WHEN now() - ts > interval '1 hour'
      THEN date_part('hour', now() - ts) || ' hours ago'
      ELSE 'less then 1 hour ago'
    END
$$ LANGUAGE sql;

Returning SQL Results as JSON

Among the glaring deficiencies of JSON is its limited and inflexible set of data types. One could simply encode everything as a string, but for data returned from PostgreSQL there is another compromise that will use a native representation when possible: row_to_json().

def fetch_sample_record(cursor, row_to_json=False):
    query = """
    SELECT 1032334 AS id, now(), 1.567699 AS voltage;
    """
    if row_to_json:
        query = "SELECT row_to_json(results) FROM (" + query + ") AS results"
        cursor.execute(query, [job_id])
        return [r[0] for r in cursor.fetchall()]
    else:
        cursor.execute(query, [job_id])
        return cursor.fetchall()