Hi all,
A few thousand rows have been deleted by accident in one of our databases.
I immediately disabled autovacuum and recovered the missing rows using pgfsck, a PostgreSQL table checker and dumper.
pgfsck can be found here:
http://svana.org/kleptog/pgsql/pgfsck.html
I am now facing a puzzling challenge: converting binary timestamp data! If pgfsck did properly recover the timestamp data as a binary string, pgfsck will use a default timestamp, “1900-01-01 00:00:00”, presumably because the date/time encoding varies from platform to platform.
Being on a FreeBSD box, and having PostgreSQL compiled with default options, I am assuming timestamps are encoded as long long (a signed long for the date and an unsigned long for the time).
I trying to unpack the string with Perl:
use strict;
my $t;
my $dt = '\xeb8^Ru^R^K\xb2A';
my @t = unpack( "Ll", $dt );
print $t[0] . "\n";
print $t[1] . "\n";
What I get is:
1650817116
1968332344
That is where I am being kind of… stuck…
I would have guessed that $t[1] is the number of microseconds since 2001-01-01… but what about $t[0]… it can’t be microseconds…
I had the idea to convert the binary timestamp using unpack and gmtime:
my @d = gmtime(946684800 + (($t[1] + $t[0]) / 1000000));
sprintf "%04d-%02d-%02d %02d:%02d:%02d", $d[5]+1900, $d[4]+1, $d[3], $d[2], $d[1], $d[0];
946684800 being the number of seconds from 1970-01-01 and 2001-01-01…
Any idea would be greatly appreciated!
De bedste hilsner / Best regards
David De Maeyer
Developer / System Architect
-------------------------------------------
ADNUVO°
Kompagnistræde 34
1208 Copenhagen K
Denmark
-------------------------------------------
