I'm storing data in PostgreSQL with one column set to a default CURRENT_TIMESTAMP
. I'm trying to get this default to only store: date, hours and minutes. Does anyone know how I can do it?
I need to be able to manipulate the data using Ruby to then display it on an html page. At the moment I can display a timestamp like this: 2019-09-15 12:50:05.745811
but would like something more like this: 2019-09-15 12:50
.
At the moment I'm extracting each row of a table into a Ruby object and defining each value as accessible instance variable so @id = id
, @content = content
and @timestamp = timestamp
. I would like a way to either edit a string like this: 2019-09-15 12:50:05.745811
in ruby or what I guess might be easier to find a way for the DEFAULT CURRENT_TIMESTAMP
to only store the relevant data. Any help, much appreciated.
This is how my table has been created:
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
content VARCHAR(280),
timestamp timestamp DEFAULT CURRENT_TIMESTAMP
);
If you always want your date resolution only to the second then you can specify that in the definition:
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
content VARCHAR(280),
timestamp timestamp(0) DEFAULT CURRENT_TIMESTAMP
);
If you want the capability to process and/or display fractional seconds then make the conversion when selecting:
Select ..., timestamp::timestamp(0) ...
When you want to show less the second precision then you must use to_char(...) and specify the mask for what you want, So in your example 2019-09-15 12:50:
select ... to_char(timestamp, 'yyyy-mm-dd hh24:mi:'), ...
IMHO it is very bad practice to name a column the same as a data type. It will at sometime cause confusion or mistakes.