Re: Datetime stored in bigint

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: pgsql-novice(at)postgresql(dot)org Content-Type: multipart/alternative; boundary=00163683238ca071dd04a85dddd6 X-Virus-Scanned: Maia Mailguard 1(dot)0(dot)1 X-Mailing-List: pgsql-novice List-Archive: <http://archives(dot)postgresql(dot)org/pgsql-novice> List-Help: <mailto:majordomo(at)postgresql(dot)org?body=help> List-ID: <pgsql-novice(dot)postgresql(dot)org> List-Owner: <mailto:pgsql-novice-owner(at)postgresql(dot)org> List-Post: <mailto:pgsql-novice(at)postgresql(dot)org> List-Subscribe: <mailto:majordomo(at)postgresql(dot)org?body=sub%20pgsql-novice> List-Unsubssdev938(at)gmail(dot)com
Subject: Re: Datetime stored in bigint
Date: 2011-07-21 17:39:09
Message-ID: 0799d7d42d6a3e15e954899916321e96@biglumber.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> Its an EMR and from the front end i see 7/11/2011 4:29:41 PM
> and in the database i see 634459985818906250.

Okay, that helps a little bit more. Working backwards, I determined
that this is the number of nanoseconds since year 0, i.e. when
we switched from BC to AD. Thus, we can divide out the nanoseconds,
compute the number of hours, and get the date we want. Here's a
quick function to do just that:

CREATE OR REPLACE FUNCTION yearzero_to_timestamp(BIGINT)
RETURNS TIMESTAMP
IMMUTABLE
LANGUAGE SQL
AS $bc$
SELECT '0001-01-01'::date + ('1 hour'::interval * (SELECT $1/10000000/60/60.0));
$bc$;

SELECT yearzero_to_timestamp(634459985818906250);

yearzero_to_timestamp
- -----------------------
2011-07-11 16:29:00

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201107211337
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk4oZAsACgkQvJuQZxSWSsiyEwCfWFRaifq1fNKVNuOzNMWvVKD9
hOoAn1+GrDLb/Q97VS51NaXeD+mtpSNQ
=H45T
-----END PGP SIGNATURE-----

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Vic Ross 2011-07-22 11:01:25 org.postgresql.util.PSQLException: The user property is missing. It is mandatory.
Previous Message einkauf2 2011-07-19 10:47:55 Change language settings