On 05/30/2012 01:48 PM, David Salisbury wrote:
On 5/30/12 9:42 AM, Adrian Klaver wrote:
Think I realize where the confusion is now. When Jasen mentioned integer
datetimes he was referring to the internal storage format Postgres uses
to record the datetime value. Via the magic of programming(others will
have to fill that part in) the internal format can represent time down
to microseconds even though the value is actually stored as an
eight-byte integer. When you do an explicit cast of a timestamp value to
integer you are asking that the value be only a whole number and the
decimal portion is discarded. In other words the internal integer
encodes the decimal values the external integer does not.
Thanks! I was looking for some sort of verification along these lines.
So in my mind, the internal storage of a timestamp would be the number
of milliseconds since 1970 ( or similar ). But to me, if I cast something
that is an integer into an integer it would still be an integer ;) , and
still hold the milliseconds. Perhaps if I cast a datetime into a bigint
it'll
still hold the number of ms? Some sort of parameter setting for dates
would be nice to be able to default a date/time format down to the ms, w/o
having to explicitly format it with every select... imho
The client does not see the internal value. That value is decoded when
presented to the client and encoded when the client presents a datetime
value. Any integer casting you do as the client will not change that. As
to datetime formatting, I believe that was covered in another thread:)
test=> SELECT now();
now
-------------------------------
2012-05-30 14:25:28.719475-07
(1 row)
test=> SELECT '2012-05-30'::TIMESTAMP;
timestamp
---------------------
2012-05-30 00:00:00
(1 row)
test=> SELECT '2012-05-30 2:26:45.56789'::TIMESTAMP;
timestamp
---------------------------
2012-05-30 02:26:45.56789
(1 row)
A timestamp will return values that are significant.
-ds
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
