0

I have two columns, date and time, both strings. I can concatenate them to get a column timestamp.

I need to use the timezone_minute() function†. I have tried various formats for the parameter, but have repeatedly faced the error

Query 1 ERROR: ERROR:  function timezone_minute(timestamp without time zone) does not exist
LINE 1: select timezone_minute('2023-01-25T05:36:48Z'::date AT TIME ...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

For example

-- function timezone_minute(timestamp without time zone) does not exist
select timezone_minute('2023-01-25T05:36:48Z'::date AT TIME ZONE 'UTC');
    
-- function timezone_minute(timestamp with time zone) does not exist
select timezone_minute('2023-01-25T05:36:48Z'::date AT TIME ZONE 'UTC');

select timezone_minute('2023-01-25T05:36:48Z'::timestamptz);
select timezone_minute(cast('2023-01-25T05:36:48Z' as TIMESTAMP));

-- etc

How do I correctly utilise timezone_minute - the documentation is severely lacking for this particular function, unfortunately.

† I don't fully control the final query, hence the need to use this function. The generated query utilises timezone_minute(), I can essentially set the argument.

Ben Swinburne
  • 25,669
  • 10
  • 69
  • 108
  • "*have two columns, date and time, both strings*" - that's a really, really bad idea. You should fix that before you continue. –  Jan 25 '23 at 07:33
  • @a_horse_with_no_name Thanks, I'm aware that this is vey bad and it will be fixed. The data comes from some logs and is in that format (end query will run on Athena/Presto). I'm testing out a BI tool so just running it against a tiny sample in Postgres with its Postgres driver for convenience. I noted the format in case it affected how i should be converting it – Ben Swinburne Jan 25 '23 at 16:16

1 Answers1

1

the documentation is severely lacking for this particular function, unfortunately.

Yes, because it's not a function.

It is an argument to the extract() function:

select extract(timezone_minute from '2023-01-25T05:36:48Z'::timestamptz);
  • Ahha! I'd tried `select extract(timezone_minute from timestamp '2023-01-25T05:36:48Z'::timezonetz);` thinking that but had a random timezone keyword in it. Interesting that the query generated uses it as a function rather than an argument. I'll have to contact the vendor. Thanks for your answer – Ben Swinburne Jan 25 '23 at 16:18
  • It was falling back to the Athena driver (which was default) due to a misconfiguration, and was generating queries compatible with Athena utilising `timestamp_minute()` as a function. – Ben Swinburne Jan 26 '23 at 23:18