sql - Two questions for formatting timestamp and number using postgressql -
i selecting date column in format "yyyy-mm-dd". i want cast timestamp such "yyyy-mm-dd hh:mm:ss:ms" i attempted: select cast(mycolumn timestamp) mytable; but resulted in format yyyy-mm-dd hh:mm:ss i tried select to_timestamp(mycolumn,yyyy-mm-dd hh:mm:ss:ms) mytable; but did not work either. cannot seem figure out correct way format this. note want first digit of milliseconds. //////////////second question i trying select numeric data such there not trailing zeros. for example, if have values in table such 1, 2.00, 3.34, 4.50. i want able select values 1, 2, 3.34, 4.5. i tried using ::float, strange output. tried rounding function, how use without knowing how many decimal points need before hand? thanks help! it seems functions to_timestamp() , to_char() unfortunately not perfect. if cannot find better, use these workarounds: with example_data(d) ( values ('2016-02-02') ) select d, d::timestamp || '.0...