databasepostgresqltable-plus

Is there a way to get CURRENT_TIMESTAMP to only store date, hours and minutes in a database?


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
);

Solution

  • 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.