Two Questions For Formatting Timestamp And Number Using Postgresql

I am selecting a date column which is in the format 'YYYY-MM-DD'. I want to cast it to a timestamp such that it will be 'YYYY-MM-DD HH:MM:SS:MS' I attempted: select CAST(mycolumn a

Solution 1:

It seems that the functions to_timestamp() and to_char() are unfortunately not perfect. If you cannot find anything better, use these workarounds:

with example_data(d) as (
    values ('2016-02-02')
    )
select d, d::timestamp||'.0' tstamp
from example_data;

     d      |        tstamp         
------------+-----------------------2016-02-02|2016-02-0200:00:00.0
(1row)

createfunction my_to_char(numeric)
returns text languagesqlas $$
    selectcasewhen strpos($1::text, '.') =0then $1::text
        else rtrim($1::text, '.0')
    end
$$;

with example_data(n) as (
    values (100), (2.00), (3.34), (4.50))
select n::text, my_to_char(n)
from example_data;

  n   | my_to_char 
------+------------100|1002.00|23.34|3.344.50|4.5
(4rows)

See also: How to remove the dot in to_char if the number is an integer

Solution 2:

SELECT to_char(current_timestamp, 'YYYY-MM-DD HH:MI:SS:MS');

prints

2016-02-05 03:21:18:346

Solution 3:

just add ::timestamp without time zone

select mycolumn::timestampwithouttime zone  from mytable;