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 ------------------------------------------- web : www.adnuvo.com <http://www.adnuvo.com/> mail : da...@adnuvo.com <mailto:christ...@adnuvo.com> phone : (+45) 3910 1000 mobile : (+45) 2724 6077 fax : (+45) 3910 1001 ADNUVO° Kompagnistræde 34 1208 Copenhagen K Denmark -------------------------------------------
