Search Postgresql Archives

binary timestamp conversion

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

mail

 :  

david@xxxxxxxxxx

phone

 :  

(+45) 3910 1000

mobile

 :  

(+45) 2724 6077

fax

 :  

(+45) 3910 1001


ADNUVO°
Kompagnistræde 34
1208 Copenhagen K
Denmark

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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux