Обсуждение: TIMESTAMP difference

Hi, is there a way to get the difference in hours between two
timestamps? The HH{1,}:MM:SS format will do.

foo=> select timestamp '20071211 00:00' - timestamp '20071210 00:01';
  ?column?
----------
  23:59:00
(1 row)

foo=> select timestamp '20071211 00:00' - timestamp '20071209 01:00';
     ?column?
----------------
  1 day 23:00:00
(1 row)

Any way to make it return "47:00:00" instead? select interval '47:00:00'
is still a legal interval as far as postgresql goes.

8.3-beta2 (can't get to beta4: freebsd ports are yet frozen :(

Thanks.

On Dec 10, 2007 2:13 PM, rihad <rihad@mail.ru> wrote:
> Hi, is there a way to get the difference in hours between two
> timestamps?

SELECT (EXTRACT (EPOCH FROM TIMESTAMP '20071211 00:00') - EXTRACT
(EPOCH FROM TIMESTAMP '20071209 01:00')) * INTERVAL '1 second';

Hi first post here, I hope you can help.

We are running 8.1 on Windows 2003 server and have had a server crash
over the weekend. A virus is suspected - we maintain an app server on
someone else's network, though we do have anti-virus running, the
symptoms were worrying - so we had to wipe and reinstall the OS and all
programs. We had recovered the 8.1 folder and the data off the crashed
computer prior to the wipe, but the last good .backup file is a few days
older than that.

Are there step by step instructions on restoring from the folder itself
instead of a backup file? Is it even possible?

Thanks,

Keith

am  Mon, dem 10.12.2007, um 12:30:14 -0800 mailte Keith Turner folgendes:
> Hi first post here, I hope you can help.
>
> We are running 8.1 on Windows 2003 server and have had a server crash
> over the weekend. A virus is suspected - we maintain an app server on

Please don't hijack other threads, the original thread was 'TIMESTAMP
difference'.

(don't answer to an arbitrary other mail and change the subject. Every
mail contains references-header)



> Are there step by step instructions on restoring from the folder itself
> instead of a backup file? Is it even possible?

not really...


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On 12/10/07, Keith Turner <kturner@cloudsystems.com> wrote:

> We are running 8.1 on Windows 2003 server and have had a server crash
> over the weekend. A virus is suspected - we maintain an app server on
> someone else's network, though we do have anti-virus running, the
> symptoms were worrying - so we had to wipe and reinstall the OS and all
> programs. We had recovered the 8.1 folder and the data off the crashed
> computer prior to the wipe, but the last good .backup file is a few days
> older than that.
>
> Are there step by step instructions on restoring from the folder itself
> instead of a backup file? Is it even possible?

I would try installing 8.1 again, stop the service, _copy_ your data
and config over the top of it, then start it again and see what
happens. Any problems with that should show up immediately in the
logs.

And if all else fails, you can just nuke the attempt and restore from
the older backups.

On 12/10/07, Keith Turner <kturner@cloudsystems.com> wrote:

> We are running 8.1 on Windows 2003 server and have had a server crash
> over the weekend. A virus is suspected - we maintain an app server on
> someone else's network, though we do have anti-virus running, the
> symptoms were worrying - so we had to wipe and reinstall the OS and
all
> programs. We had recovered the 8.1 folder and the data off the crashed
> computer prior to the wipe, but the last good .backup file is a few
days
> older than that.
>
> Are there step by step instructions on restoring from the folder
itself
> instead of a backup file? Is it even possible?

Trevor Talbot wrote:
>I would try installing 8.1 again, stop the service, _copy_ your data
>and config over the top of it, then start it again and see what
>happens. Any problems with that should show up immediately in the
>logs.

>And if all else fails, you can just nuke the attempt and restore from
>the older backups.

I did this - it took a couple of tries, but it worked.

For the record, here's a description of the problem and solution:

Problem (before I became involved): Someone had turned off RPC service
while trying to fix something. Someone else rebooted the server and all
the windows services failed on restart. Panicked and suspecting a virus
they reinstalled the OS, but had the wit to savethe postgres folders but
couldn't make a .backup file. And the last backup file was the night
before, and they had done a lot of work that would be lost if that was
used.

Solution:
It wasn't a simple matter of just copying over the data folder. There
are reasons having to do with not letting the client see you sweat and
to do it in the BG. So on a fresh system we did as suggested above,
copied over the files. One step that seems to be required (at least in
this case) was a clean new install of postgres, stopping it immediately
after the install, not one that had been used in any way.

Required: The postgres files from the crashed server.

Step 1.
Clean install of same version of Postgres (8.1) on second computer using
same superuser and password.

Step 2.
Stop postgtres service

Step 3.
Copy over Data folder replacing the one that was created on install

Step 4.
Start postgres

Step 5.
Log in to pgadmin, confirm that the data is there

Step 6.
Create a .backup that can be restored on the live site.

Step 7.
Create the database on the live site and restore from the .backup file.

Thanks for your suggestion. We had tried it and failed, but this
reinforced that it might work, so we then tried with a fresh install as
well, and were delighted to have our data back.

Regards,

Keith