Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group