I have a logins table in the following (simplified) structure: Now I want to generate chart like this: https://prnt.sc/mifz6y Basically I want to show the logins within the past 48 hours. My current query: This works as long as there are entries for every hour. However, if in some hour there were no logins, there will be no entry selected,
Tag: postgresql
SQL refer Primary Key in same Table and Query
I am maintaining a table of Items (Id is Primary Key). Some Items have Parent Item ( Which is also an Item, the colum Parent holds the Id of the parent Item, which is also in the same table. Some …
Count as duplicates similar values
I have following tables: Orders order_id 9 10 11 Order_details order_id, product_id 9, 7 10, 5 10, 6 11, 6 11, …
Find most recent row per user and day in sql query
I want to find the most recent purchase per customer for each date (a customer might make multiple purchases on a given date and I want the most recent for that date). My dataframe looks something …
How to update two columns in one column with two different ID and different value in single SQL query?
I have a table Item: id | price —+——– 1 | 20000 2 | 30000 3 | 40000 4 | 43000 How to update the price column id(1) = 1000, id(2) = 2000 in a single SQL query?
Can PostgreSQL JOIN on jsonb array objects?
I am considering switching to PostgreSQL, because of the JSON support. However, I am wondering, if the following would be possible with a single query: Let’s say there are two tables: Table 1) organisations: Table 2) users: Now I want to get a result like this (all i have is the ID of the organisation [1]): I know this is
showing columns in a particular pattern in postgresql
i have written a query which returns a table showing monthly total working hours of each person in the company. so the result is: name*****jan************feb**********march ……… dec Tom 170:…
How to order the query result based on following condition?
Let’s suppose I have a table as below: employee | period | commission A | 1Y | 100 A | 2Y | 150 B | 1Y | 80 C | 1Y | 200 C | 2Y | 270 C …
Postgres convert date time difference into HH:MM:SS
Ex: Diff is 0 years 0 months 5 days 20 hours 6 minutes 30 seconds. I want to convert this into HH:MM:SS format : 120:06:30. Answer tl;dr java.time Doing it on the Java side is simple with a column of a data type akin to the SQL-standard TIMESTAMP WITH TIME ZONE and driver compliant with JDBC 4.2 or later (for
SQL – Fetch associated data from multiple table
I have 4 tables. The table does not contain any foreign key reference t1: | id | name | +—–+—————-+ | a1 | cheese | | a2 | butter | | a3 | milk | …