Month: June 2007

Another little tidbit.. I use this frequently to translate epoch timestamps from a Postgres database log table:

#!/usr/bin/perl
# translate epoch time to human readable local time
use strict;
use warnings;
my $epoch = $ARGV[0];
if ( $epoch ) {
print "Epoch time: ".$epoch."\n";
print "Local time: ".localtime($epoch)."\n";
} else {
print "This script takes an argument of an epoch timestamp and translates it to local time:\n\n";
print " perl epoch2local.pl 1131662204\n\n";
}

CSV Output from ‘psql’ – courtesy of Will R.

#!/bin/bash
FS="'|field_separator|'"
RS="'|record_separator|'"
HOST="localhost"
DB="core"
USER="core_write"
FILE=$1
psql -A -F$FS -R$RS -f$FILE -h$HOST -d$DB -U$USER \
| sed 's/"/""/g' \
| sed "s/$FS/\",\"/g" \
| sed "s/$RS/\"\n\"/g" \
| sed '1s/^\(.*\)$/"\1/' \
| grep -v "^\"([0123456789]\+ row[s]\?)$"

This script will properly escape commas, quotes, and newlines. It will also leave the headers, but remove the row counts. This is useful for generating true csv output from the command line for automated reports (without requiring python and psycopg).

Save the script (ie. psql2csv.sh), make it executable, place your query in a file (ie. query.sql), then run:

./psql2csv.sh query.sql > report.csv