252 questions
0
votes
3
answers
77
views
Most recent status of each item as of the 1st of each month
I have a table that is structured in the following way: fiddle
create table test(id,status,datechange)as values
('011AVN', 11, '2024-06-21 08:27:13'::timestamp)
,('011AVN', 12, '2024-06-21 08:28:16')
...
- 23
2
votes
1
answer
82
views
How to calculate average stock status in day
Stock status for days is in table
create table stockstatus (
stockdate date not null, -- date of stock status
product character(60) not null, -- product id
status int not null, -- stock status in ...
- 28.3k
1
vote
2
answers
129
views
Generate a Series of Numbers
I am trying to generate random serial numbers in PostgreSQL with some conditions.
The numbers need to start with the same 3 letters, YAK.
After YAK, there needs to be 8 random digits.
I only need to ...
- 57
1
vote
1
answer
113
views
Postgres split time ranges into 1 minute slots, without rounding boundaries
I have a postgres table with timestamp columns:
start_datetime
end_datetime
duration
id
2021-10-17 03:13:00
2021-10-17 03:15:02
302
6214550
2021-10-17 03:15:02
2021-10-17 03:17:03
4,021
6214551
which ...
- 334
3
votes
2
answers
96
views
Generate all years between two timestamps
As part of a larger Postgres query, I generate all years between two given timestamps (timestamptz):
select to_char(generate_series, 'YYYY') from generate_series(
'2022-06-14 11:00:00.000+00'::...
- 138
1
vote
1
answer
106
views
How to pass dynamic value to a FOR loop?
I can do this:
for i in 1 .. (select pays from schema.table_name where pk_id='12' and pk2_id='123')
But it only works for this particular row. How to do it for all rows in the table?
Getting below ...
- 11
1
vote
1
answer
43
views
Generate table data from other table
I am generating mock data for a table "patients" using the following script:
WITH vars (tenantId, patientCount) as (
values (5, 30)
)
INSERT INTO patient (tenant_id, id, name)
SELECT
...
- 1,722
1
vote
1
answer
30
views
PostgreSQL regrouping after a generate_series while joining diferent tables with possible null values
In my database I have various tables with a primary_key, start_date and end_date
I'm trying to make a view table joining some of these periods so I can send this table to a PowerBI and use it.
My ...
4
votes
1
answer
253
views
how to generate sequence of values in SQLite when lower and upper bounds are taken from another table?
I can use SQLite's built-in generate_sequence function to create a series of values with a known (constant) lower and upper bound:
sqlite> select value from generate_series(1, 5);
1
2
3
4
5
How do ...
- 1,413
-1
votes
1
answer
52
views
Query does not show current month's data
SELECT TO_CHAR(months.month, 'YYYY-MM') AS month,
COALESCE(SUM(dm.revenue)::FLOAT, 0.0) AS total_amount,
COALESCE(COUNT(dm.id), 0) AS value,
array_agg(dm.id) AS lost_deal_ids
FROM ...
0
votes
0
answers
19
views
generate_series() failed in Redshift [duplicate]
I worked well in DBeaver with code
INSERT INTO table_dates(calendar_date, day_of_week)
SELECT
generate_series::date as calendar_date,
To_Char(generate_series, 'Dy') as. day_of_week
FROM ...
- 1
-2
votes
1
answer
179
views
FOR loop inside json_build_array() function does not work
I am currently working on a function in Postgres. I have the function working, but I need to add a loop inside the json_build_array() function to add multiple of what is inside the array. This is what ...
- 37
1
vote
2
answers
315
views
How to round up when using generate_series()?
I've have a Postgres database and I'm using Grafana to visualise some data.
For instance, my Grafana panel has the following variables where the values are dynamic:
$__timeFrom() = '2023-10-21T00:00:...
- 15k
1
vote
1
answer
103
views
Perform a query on each CTE rows (generate_series())
I would like to know if it is possible to perform a query on each rows of a CTE :
with series as (
select (d + '1 month'::interval - '1 day'::interval )::timestamp date
from generate_series(...
- 45
0
votes
1
answer
120
views
Generate date series with hours of 'OFF' type for each day
I am writing a #PostgreSQL query for my company to calculate how many hours a person is OFF working, with a table containing information about requests, with these columns: Request ID, Sender username,...
1
vote
1
answer
101
views
Is it possible to avoid CASE ... WHEN in generate_series statement when two different beginning dates are required
I have one query that I use for different date sequence generation depending on the query parameters from the backend.
Gist for it is that for the first month, I need date to be generate_series input ...
0
votes
2
answers
1k
views
Generate a sequence of numbers for a certain interval in vertica
Generate a sequence of numbers for a certain interval.
In postgresql there is a function generate_series.
You need to write something similar to sql for vertica
id
low_esn
high_esn
101
10
13
102
5
7
...
- 5
0
votes
1
answer
134
views
Postgres generate_series how to exclude last day when hour is 00:00
I need to generate a series of days in postgresql that would produce different result depending on the hours in the timestamp.
My series generation works fine when the time is not midnight.
For time ...
- 7
0
votes
1
answer
563
views
In PostgreSQL, how to get number of event per day between a period including days without events (0 values)
I need to find the way to get the number of events that happens in every day between a period, including days in which no event occurs, getting that day a 0 value.
I try this query:
SELECT f.day, ...
- 301
2
votes
1
answer
182
views
How to use generate_series to get the sum of values in a weekly interval
I'm having trouble using generate_series in a weekly interval. I have two examples here, one is in a monthly interval and it is working as expected. It is returning each month and the sum of the facts....
- 483
0
votes
1
answer
87
views
Group by arbitrary interval
I have a column that is of type timestamp. I would like to dynamically group the results by random period time (it can be 10 seconds or even 5 hours).
Supposing, I have this kind of data:
Image
If the ...
1
vote
1
answer
55
views
Count per day per category using generate_series()
I am trying to generate using generate_series() for each day and each category, the count, in a given date range.
Table Posts:
id
date
category_id
1
2022-01-01
1
2
2022-01-01
1
3
2022-01-02
1
4
2022-...
- 384
0
votes
1
answer
1k
views
How to generate series of dates in postgresql that are repeated every end of the month of another date
I want to generate date data using postgresql function "generate_series" , however I have only advanced as far as the following:
SELECT
( DATE_TRUNC( 'month', ld ) + '1 month'::INTERVAL -...
- 33
1
vote
2
answers
587
views
PostgreSQL - GENERATE_SERIES issue - simulating data
I thought I understood GENERATE_SERIES(), but obviously not as well as I thought.
I have a table:
CREATE TABLE status
(
start_tz TIMESTAMPTZ NOT NULL,
end_tz TIMESTAMPTZ NOT NULL,
ms ...
- 1,004
0
votes
2
answers
205
views
PostgreSQL Attendance and night shift
i have following table:
dt
type
2022-09-12 21:36:26
WORK_START
2022-09-13 02:00:00
BREAK_START
2022-09-20 06:00:00
WORK_START
2022-09-20 10:00:00
BREAK_START
2022-09-20 10:27:00
BREAK_END
2022-09-20 ...
- 11
1
vote
0
answers
106
views
Postgres generate_series wrong result
I don't know if it's a bug or it's intended, but the simple query
select generate_series('2021-03-28'::date, '2021-03-29'::date - interval '1 minute', interval '1 hour') as date_hour;
> 2021-03-28 ...
- 1,455
0
votes
1
answer
91
views
Redshift, error in create a table with generated series
I'm trying to save a generated date series in a temporary table on redshit:
drop table if exists date_list;
create temporary table date_list as
with seq as(
select date '2020-02-27' + i as dt
from ...
- 841
1
vote
1
answer
152
views
"ERROR: syntax error at end of input" when running Do command
I was trying to create a calendar table. The query below. I get the error in the title. Can someone explain why?
create table if not exists test_calendar (test_date date primary key);
do $$
declare
...
- 11
0
votes
0
answers
87
views
LEFT OUTER JOIN not producing full output on time series CTE using generate_series (postgresql)
I was hoping to have a CTE that generated some time series data (hourly values based on min / max timestamp values pulled from another table) then doing a LEFT OUTER JOIN on a table of transaction ...
- 505
0
votes
1
answer
160
views
Generate series of numbers based on input values in Redshift
I want to generate a series of numbers based on input values in fields in a table (check the image). I found a solution for this in Postgres but unfortunately, it seems that Redshift doesn't support ...
- 1
0
votes
2
answers
149
views
Generating multiple different times over different days with generate_series
I am trying to generate different length time intervals e.g. 0-5am, 10am-1pm, 6-8pm that occur daily, so something like this:
+--------------------------+--------------------------+
|start ...
- 3,283
1
vote
1
answer
1k
views
Generate int4range from a set of integer values selecting as a specific table and column
I am trying to generate a set of int4range values, but instead of getting int4range, I'm getting type record. I can't seem to extract the range or cast to the range type.
I specifically want to keep ...
- 719
-1
votes
1
answer
65
views
Select month between 2 date in postgresql
For example I have a report that requires February have column Created_date start at 2022-01-26 and end at 2022-02-25
How would I set up a query such that I retrieve the following table as a response:
...
- 71
2
votes
2
answers
2k
views
how to join two tables with generate series
I have two tables user and attendance. I want to get attendance for all users within specific time interval even though users doesn't have attendance on some dates like public holidays, ...
I used ...
- 72
0
votes
2
answers
344
views
PostgreSQL to get the xth business day for the given month
Get xth Business day of a calendar month. For ex. if Nov'21 then 3rd business day is 3rd November, but if Oct'21 3rd business day is 5th Oct. We need to build a query or function to get this ...
- 1
2
votes
2
answers
1k
views
Sql group query results by user id and date ranges dynamically
I have a query that collects information from different tables for a particular time range.
Currently, I make requests for every user and every date range separately, but I would like to run it for ...
- 33
0
votes
2
answers
238
views
How to get up to last record from Generate series from 00 - 23:59 time but with 15 minutes interval
I am trying to use generate_series in postgres so that I can get all the last record from 00:00 - 23:59 with 15 minutes interval, the problem is, I only receive 23:45 for the last record, how does it ...
- 1
0
votes
1
answer
296
views
The correct way to get results in Generate series in postgres for time
I want to use generate_series function in postgres so that I can get data like this, the idea is to get range interval with 1 second substraction
[
{
"generate_series": &...
- 1
-1
votes
2
answers
88
views
Get count of susbcribers for each month in current year even if count is 0
I need to get the count of new subscribers each month of the current year.
DB Structure: Subscriber(subscriber_id, create_timestamp, ...)
Expected result:
date | count
-----------+------
2021-01-...
- 63
0
votes
1
answer
131
views
Automatically increasing the value of a variable in generate_series
I ran into the need to automatically increment the value of a variable in generate_series. By the condition of the business logic, the field identifier in the model does not increase automatically.
I ...
- 73
0
votes
1
answer
363
views
Modify Postgres query to use generate_series for overall summation over each of several consecutive range intervals
I'm still quite new with SQL, coming from an ORM-centric environment, so please be patient with me.
Provided with a table in the form of:
CREATE TABLE event (id int, order_dates tsrange, flow int);
...
- 719
2
votes
1
answer
459
views
Generate timestamp ranges over an interval and query for all overlapping events
I want to generate a series of timestamp ranges over an interval, and query a table for all of the overlapping events. I am almost there, but still pretty new to SQL and cannot seem to get over this ...
- 719
0
votes
1
answer
499
views
Filtering a query that uses generate_series while keeping null rows
I'm performing a raw query in Django in order to make use of PostgreSQL's generate_series function and get rows back for each date in an interval, comparing the generated dates with datetime ranges (...
- 719
1
vote
1
answer
417
views
How to generate random data on an associated table in postgres?
I have two tables: users and results. A user has many results.
I need to generate 10 million records in our users table and I was able to do this using the generate_series function in postgres.
Now, I ...
- 528
0
votes
1
answer
1k
views
Imitate behaviour of "generate_series" with SQL
I am trying to imitate the behaviour of "generate_series".
My table contains various fields. One of these is "count". I want to output each row as often as "count" ...
- 45
-1
votes
1
answer
76
views
Optimization postgresql request
How I can optimize request in postgresql?
What the best indexes I should create?
I have tried to add separate index for t_my.id,t_my.param1,t_my.param2, t_my_other.param3 but my request work slowly.
...
- 65
2
votes
2
answers
203
views
find date gap in postgres
i have a table with userIds and some dates like this : -
| userId | dates |
| 1 | 2021-06-20 00:00:00|
| 1 | 2021-06-24 00:00:00|
| 2 | 2021-06-25 00:00:00|
...
1
vote
1
answer
1k
views
Redshift - generate_series workaround?
I have a table for machine installations in this way:
installationID, machineID, installed_at, uninstalled_at
A, 1, 2020-01-01, Null
B, 2, 2020-01-01, 2020-01-02
C, 3, 2020-01-02, Null
D, 2, 2020-01-...
- 1,337
1
vote
1
answer
35
views
Rainfall ranged series for each category in PostgresSQL
We have POPULATION and AGE_GROUP tables.
Table : POPULATION
Has entries as follows.
village
age_group
male_count
female_count
A
0-4
4
2
A
5-9
3
4
A
10-14
4
9
A
15-19
8
6
A
25-29
8
6
Like this we have ...
- 2,097
0
votes
1
answer
785
views
Microsoft Power BI DAX - Convert a table with a range of integer values in two columns, into a new table with a row for each value in the range
I have a situation in which I am using Microsoft Power BI. I have a source table (called Couriers), with a range of weights (MinWeight to MaxWeight) for any given combination of Courier and Country, ...
- 465