Thread: binary timestamp conversion

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

-------------------------------------------