On 08/05/2015 09:16 AM, Murali M wrote:
Hi everyone,
First of all, let me thank all of you for the very informative
discussion. I will say my solution was to declare the field YYYYMMDDHH24
as int (can handle till Dec 31, 2147, Hr23 -- which will be 2147123123).
Also this way, I can still use between etc to select a range of dates..
of course, I will miss validation.. I believe it will work for me to the
best of my knowledge. (let me know if you have experiences with storing
time as int and there are issues I have not thought of)..
postgres@production=# select to_date('201508051314', 'YYYYMMDDHH24MI') -
to_date('201508041314', 'YYYYMMDDHH24MI');
?column?
----------
1
(1 row)
postgres@production=# select 201508051314 - 201508041314;
?column?
----------
10000
(1 row)
postgres@production=# select 201508051314::timestamp -
201508041314::timestamp;
ERROR: cannot cast type bigint to timestamp without time zone
LINE 1: select 201508051314::timestamp - 201508041314::timestamp;
So it comes down to where you want to spend the time, doing a one time
convert on import or do conversions every time you want to use the data
as a timestamp instead of the type it is stored as.
thanks, murali.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
