|
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
|
