I have a table ,one of the table filed is json format: my_table id json_field 1 { "to_status": 7, "to_status_name": "In Progress", "role": "admin"} 2 { "to_status": 3, "to_status_name": "Completed", "role": "admin"} 3 { "to_status": 2, "to_status_name": "Completed", "role": "customer"}…
I am trying to convert string '2022-12-28T22:28:43.260781049Z' to datetime format. I have such query: SELECT date(str_to_date('2022-12-28T22:28:43.260781049Z','%Y-%m-%d')) as date, hour(str_to_date('2022-12-28T22:28:43.260781049Z',"%H:%M:%S")) as hour FROM transaction And such output: date time '2022-12-28' NULL How to get time as well?
This query displays count Actions grouped by day of the week. select count(*) c, date_part('dow', "updatedAt" at time zone 'Europe/Paris') dow from "Action" date_part('dow', "updatedAt" at time zone 'Europe/Paris') Days are displayed as number. Is there a way to display…
My table looks like this: from to status 1 3 invalid 10 15 valid How can I efficiently SELECT or produce a result like this: serial status 1 invalid 2 invalid 3 invalid 10 valid 11 valid 12 valid 13…
I have a table Transactions with id, type, guest_id, timestamp. I want to get the total Car sessions for the last two months. (assuming all transactions with type 'Car' within the same hour from the same guest constitute 1 session).…
I have 2 selects that just get the SUM of IDs and are grouped by month.. I would like to have them only on 1 SELECT - side by side... SELECT MONTH(data) AS month, COUNT(id) AS TOTAL FROM numeracao WHERE…
Let say I have this kind of column create table exercise (cust_id varchar, policy_id varchar, end_date date, flag varchar); insert into exercise (cust_id, policy_id, end_date, flag) values ('1', 'a1', '2022-10-12', 'inactive'), ('1', 'a2', '2022-10-20', 'inactive'), ('1', 'a2', '2023-05-15', 'active'), ('2',…
I have a column in the form of a dictionary: {"black": false, "white": true, "green": true, "blue": false, "red": true, "yellow": false, "pink": false, "orange": true} I want to turn it into a list of keys that has a true…
The function doesn't throw any error nor does it show any return value. CREATE OR REPLACE FUNCTION login(iemail VARCHAR,ipassword VARCHAR) RETURNS TABLE(id INTEGER, name VARCHAR, lastName VARCHAR, age INTEGER, mobile VARCHAR,email VARCHAR) LANGUAGE 'plpgsql' AS $$ #variable_conflict use_variable BEGIN RETURN…
I need an non-superuser to create dbs and users, and assign this users to its respective DB, but this "start" user should not be able to delete other DBs or basically nothing more than create users and DBs. I have…