0

I have a timestamp with time zone field named statdate and the entry looks like this 2021-11-17 12:47:54-08. I want to create a field with just the time of day expressed locally, so it would look like 12:47:54. (This data was recorded on an iPhone and it's 12:28 PST). (Go to bottom of post for solution using views from @AdrianKalver)

select *,statdate :: timestamp :: time as stattime  from table

works in PGAdmin and an example result is 12:47:54 as desired. How do I make this an alter table

ALTER TABLE tablename add COLUMN stattime timestamp generated always AS (select *,statdate :: timestamp :: time as stattime from tablename) stored;

is the wrong syntax.

ALTER TABLE tablename add COLUMN stattime timestamp generated always AS ( EXTRACT(HOUR FROM statdate) || ':' ||  EXTRACT(MINUTE FROM statdate) || ':' ||  EXTRACT(SECOND FROM statdate)) stored;

ERROR: generation expression is not immutable which I'm presuming is a type problem, although postgres can concatenate strings and numbers with this syntax.

Just tried something else

ALTER TABLE tablename add COLUMN stattime timestamp generated always AS ( Cast(EXTRACT(HOUR FROM statdate) as text) || ':' ||  cast(EXTRACT(MINUTE FROM statdate) as text) || ':' ||  cast(EXTRACT(SECOND FROM statdate) as text) ) stored;  -- ERROR:  generation expression is not immutable

I'm using the hours and minutes for a graph and I can't get in the middle of the Chartkick. Could do it in High Charts, but think it will be simpler to create the view chart and use that. The Rails/Chartkick looks like

<%= line_chart TableName.where(statdate: start..current_date).pluck(:statdate, :y_axis) %>

and can't break that apart. So will go with creating a View Table.

What's the right way to do this? I've looked here and at the postgresql docs and not having much luck.

Following comments, the solution

      CREATE OR REPLACE VIEW public.view_bp_with_time AS
  SELECT 
    id,
     statdate,
     statdate :: time AS stattime,
     y-axis
     FROM table_name
   ORDER BY statdate

Now to bring into Rails. Not as straightforward as I thought. And I'm off the computer for the next week.

Greg
  • 2,359
  • 5
  • 22
  • 35
  • 1
    Looks like you are trying to `REPLACE` a `VIEW` by changing its column type. That won't work. In `psql` what does `\d public.view_bp_with_time` return? FYI, `time with time zone` is pretty much useless, just use `time`. – Adrian Klaver Nov 21 '21 at 17:32
  • 1
    Also if `statdate` is `timestamp with time zone` all you have to do is `statdate::time`. – Adrian Klaver Nov 21 '21 at 17:39
  • 1
    I'm guessing because you are doing a `... REPLACE VIEW public.view_bp_with_time` on a view that already exists and has `stattime` as a text field. The `\d public.view_bp_with_time` should show you that. If that is the case you will need to `DROP` the view and run your new `CREATE OR REPLACE VIEW public.view_bp_with_time ...` – Adrian Klaver Nov 21 '21 at 17:54
  • @AdrianKlaver. OK will drop table, I thought replace would handle it. Yes `statdate` is `timestamp with time zone` and first line of OP has a sample. I updated OP to follow your suggestion and still getting an error. `\d public.view_bp_with_time`: Null display is "NULL". View "public.view_bp_with_time" id | bigint statdate | timestamp with time zone stattime | text y-axis | integer – Greg Nov 21 '21 at 17:55
  • 1
    No, don't `DROP` the table, `DROP` the view. – Adrian Klaver Nov 21 '21 at 17:56
  • When try to view the view, getting an error: `ERROR: more than one row returned by a subquery used as an expression`. Thank you. I repeated `DROP VIEW public.view_bp_with_time` and created the view again with the same error. – Greg Nov 21 '21 at 18:07
  • 1
    My mistake, I missed that. It should be just the line `statdate :: time AS stattime`, no `SELECT`. – Adrian Klaver Nov 21 '21 at 18:10
  • Mistakes are allowed. That did it. I'll have to review all of this so I see what's going on. But this is a much better overall solution to what I was trying to do. – Greg Nov 21 '21 at 18:16

1 Answers1

1

Per here:

https://www.postgresql.org/docs/current/sql-createtable.html

GENERATED ALWAYS AS ( generation_expr ) STORED

This clause creates the column as a generated column. The column cannot be written to, and when read the result of the specified expression will be returned.

The keyword STORED is required to signify that the column will be computed on write and will be stored on disk.

The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed.

Basically the cast from timestamptz to timestamp is not immutable as there are time zones involved.

For more information see:

https://www.postgresql.org/docs/14/xfunc-volatility.html

Either:

  1. Create a view that does the conversion.

  2. Include it in your query as you show for the pgAdmin4 example.

  3. Create a timestamp field on the table and either add the value to that field as part of INSERT\UPDATE or add a trigger that does that.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • You're saying I can't populate the column when creating it? Will have to review views, although I've used them. Added to OP on why I don't think item 2 will work. Thank you for the suggestions. – Greg Nov 21 '21 at 04:55