使用PostgreSQL格式化时间戳和数字的两个问题 [英] Two questions for formatting timestamp and number using postgresql
问题描述
我选择的日期列的格式为 YYYY-MM-DD。
I am selecting a date column which is in the format "YYYY-MM-DD".
我想将其强制转换为时间戳,以便 YYYY-MM-DD HH:MM:SS:MS
I want to cast it to a timestamp such that it will be "YYYY-MM-DD HH:MM:SS:MS"
我尝试过:
select CAST(mycolumn as timestamp) from mytable;
但这导致格式为YYYY-MM-DD HH:MM:SS
but this resulted in the format YYYY-MM-DD HH:MM:SS
我也尝试过
select TO_TIMESTAMP(mycolumn,YYYY-MM-DD HH:MM:SS:MS) from mytable;
,但这也不起作用。我似乎无法找出正确的格式化方式。请注意,我只想要毫秒的第一位数字。
but this did not work either. I cannot seem to figure out the correct way to format this. Note that I only want the first digit of the milliseconds.
//////////////第二个问题
//////////////second question
我也在尝试选择数字数据,这样就不会有任何结尾的零。
I am also trying to select numeric data such that there will not be any trailing zeros.
例如,如果我在表中有值,例如1、2.00、3.34、4.50。
For example, if I have values in a table such as 1, 2.00, 3.34, 4.50.
我希望能够将这些值选择为1、2、3.34、4.5。
I want to be able to select those values as 1, 2, 3.34, 4.5.
我尝试使用:: float,但有时会出现奇怪的输出。我也尝试过舍入功能,但是如何在不知道自己需要多少小数位的情况下正确使用它?
I tried using ::float, but I occasionally strange output. I also tried the rounding function, but how could I use it properly without knowing how many decimal points I need before hand?
感谢您的帮助!
推荐答案
似乎函数 to_timestamp()
和 to_char( )
并不完美。
如果找不到更好的方法,请使用以下变通方法:
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-02 00:00:00.0
(1 row)
create function my_to_char(numeric)
returns text language sql as $$
select case
when strpos($1::text, '.') = 0 then $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 | 100
2.00 | 2
3.34 | 3.34
4.50 | 4.5
(4 rows)
这篇关于使用PostgreSQL格式化时间戳和数字的两个问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!