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@aklaver.com
