I have a query to generate a report based on a date series that is grouped by date and employee_id. The date should be based on a particular time zone, in this case ‘Asia/Kuala_Lumpur’. But this can change depending on where the user’s time zone is. Definition and sample data for table atten…
Tag: generate-series
How to join generate_series and an array
I want to generate a list of columns using generate_series() and joining it to a specific array (a list of value, e.g. [1, 2, 3]), and my expected result looks like this: Day | Code 2019-08-01 …
Using generate_serires with partition by generate triple duplicate row
I have a table which look like this: dt type —————————– 2019-07-01 10:00:00 A 2019-07-01 10:15:00 A 2019-07-01 11:00:00 A 2019-07-01 08:30:00 …
Generate_series in Postgres from start and end date in a table
I have been trying to generate a series of dates (YYYY-MM-DD HH) from the first until the last date in a timestamp field. I’ve got the generate_series() I need, however running into an issue when trying to grab the start and end dates from a table. I have the following to give a rough idea: Postgres 9.3…
Insert multiple rows in one table based on number in another table
I am creating a database for the first time using Postgres 9.3 on MacOSX. Let’s say I have table A and B. A starts off as empty and B as filled. I would like the number of entries in column all_names in table B to equal the number for each names in table A like table B below. Thus names
Join a count query on generate_series() and retrieve Null values as ‘0’
I want to count ID’s per month using generate_series(). This query works in PostgreSQL 9.1: This is my output: But what I want to get is this output (‘0’ value in January): Months without id should be listed nevertheless. Any ideas how to solve this? Sample data: Answer Untangled, simplified…