1

I'm tying to convert timestamp field in format (YYYY-MM-DD HH:MI:SS.MS) to it's text representation. But for some reason getting different results:

If I'm trying to convert timestamp from table:

create table test_dt (dt timestamp);
insert into test_dt values ('2016-04-14 17:10:33.007');
insert into test_dt values ('2016-04-14 17:10:33');

Timestamps are getting truncated up to the seconds:

select dt::text from test_dt;
         dt          
---------------------
 2016-04-14 17:10:33
 2016-04-14 17:10:33
(2 rows)

But if Im using direct select statement, everything works:

select '2016-04-14 17:10:33.007'::timestamp::text;
         varchar         
-------------------------
 2016-04-14 17:10:33.007
(1 row)

The question is not how to convert it to the text from table and include precision, but rather:

  • what am I doing wrong?
  • why those 2 approaches returns different result?
  • what's the rational behind this behaviour?

UPDATE

as @muistooshort suggested the following command gives the correct result:

select c::text from (select '2016-04-14 17:10:33.007'::timestamp union select '2016-04-14 17:10:33'::timestamp ) as t(c);
            c            
-------------------------
 2016-04-14 17:10:33
 2016-04-14 17:10:33.007
(2 rows)

and yes test_dt does have .007 :

select * from test_dt;
           dt            
-------------------------
 2016-04-14 17:10:33
 2016-04-14 17:10:33.007
(2 rows)

Also to_char gives milliseconds from the table:

select to_char(dt, 'YYYY-MM-DD HH:MI:SS.MS') from test_dt;
         to_char         
-------------------------
 2016-04-14 05:10:33.000
 2016-04-14 05:10:33.007
(2 rows)
Vor
  • 33,215
  • 43
  • 135
  • 193
  • 1
    Wouldn't you want to use [`to_char`](http://www.postgresql.org/docs/current/static/functions-formatting.html) to convert date and time types to `text` rather than a type cast? `to_char` gives you control over the formatting/conversion at least. PS: "rational" versus "rationale", you want the latter here. – mu is too short Apr 18 '16 at 21:29
  • @muistooshort thanks for pointing to `to_char` function. Im aware of that, but was wondering more about not how to convert it, but rather what are the differences between 2 approaches. And why 1 is showing correct result but not the other. – Vor Apr 18 '16 at 21:43
  • Doesn't happen for me in PostgreSQL 9.4, must be a RedShift-ism. Does it show the truncation with `select c::text from (values ('2016-04-14 17:10:33.007'::timestamp),('2016-04-14 17:10:33'::timestamp)) as t(c)`? I'm assuming that you've checked `select dt from test_dt` to make sure you really do have the `.007` in the table. – mu is too short Apr 18 '16 at 22:05
  • @muistooshort Ive slightly modified the syntax of your expression to be "redshift" compatable and got correct result. Does it give you any clue what might be wrong? P.S and yes `test_dt` has `.007` in the table. – Vor Apr 19 '16 at 13:24
  • The only clue I have here is that RedShift is based on a truly ancient version of PostgreSQL (8.0), maybe the casting behavior has changed over the years. – mu is too short Apr 19 '16 at 16:45

0 Answers0