Re: Question about copy from with timestamp format

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