All Questions
Tagged with time postgresql
27 questions
0
votes
1
answer
26
views
Why is my mean much more bigger than my Execution Time using hyperfine to benchmark my query performance?
QUERY PLAN
-----------------------------------------------------------------------------------------------------...
- 1
1
vote
0
answers
59
views
Time of last insert into table in PostgreSQL v16
I am testing new features of PostgreSQL v16. I see two new columns are added to system tables:
pg_catalog.pg_stat_all_tables.last_seq_scan - records last sequential scan
pg_catalog....
- 401
0
votes
1
answer
118
views
PostgreSQL Real-Time Analytics
Latest months I am working with PostgreSQL while coming from Microsoft world (SQLServer). My scenario is the following:
Transfer data from different databases into single one (same set of tables and ...
- 170
1
vote
0
answers
59
views
How can I generate bar chart values for some time series data in postgresql?
I have a 'log' table which has two columns user_id and a ts (timestamp value of activity event for user):
I can use the following query to get a list of each user_id and their last activity event:
...
0
votes
0
answers
26
views
Time difference is not returning the expected result in PostgreSQL
select '06:00:00'::TIME - ('19:00:00'::TIME - '07:00:23.388471'::TIME);
+-----------------+
| ?column? |
|-----------------|
| 18:00:23.388471 |
+-----------------+
SELECT 1
Time: 0.012s
Why ...
- 259
1
vote
1
answer
43
views
Convert specific time format to common one
Environment:
I have 'duration' datas stored as varchar(4) in a table.
2 first characters are hours, 2 last characters are minutes. E.g.:
0100 means 01:00
0456 means 04:56
and so on...
What I need:
...
- 113
1
vote
1
answer
429
views
Function to compute difference between 2 times
I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I ...
- 13
1
vote
1
answer
632
views
PostgreSQL undocumented timenow() function?
Looking at the documentation of PostgreSQL I couldn't find anything on timenow(). Yet if I call the function it works.
So what is the difference between now() and timenow() ?
I am going to guess that ...
- 1,223
0
votes
1
answer
49
views
Dates, Times and Timezones - How are they incorporated in Interval?
Does interval '1 month' incorporate month differences and leap years when calculating date/datetime boundaries? If so, how does it work?
I was asked this question, and I'm not sure how to answer it. ...
- 153
1
vote
1
answer
49
views
calculate days spent of all visitors from encounters
I have an encounters table
CREATE TABLE visitors(
id serial PRIMARY KEY,
start_date TIMESTAMP NOT NULL,
-- visitor_id bigint NOT NULL -- skip for now,
end_date TIMESTAMP
);
(end_date can be ...
- 177
3
votes
1
answer
26k
views
Update value in timestamptz column
I have a table column with the data type timestamptz. Time zone of server is America/Denver. Going through some strange issue while inserting a value in that column.
When I update the column to the ...
- 185
3
votes
2
answers
10k
views
How should I represent a midnight end time?
I have a table which represents time ranges.
Column | Type | Modifiers
-------------+----------------------...
- 827
2
votes
0
answers
138
views
Why do queries on two tables with the same structure have different execution times
I have partitioned a large table in a PostgreSQL database into monthly child tables with the same structure and similar numbers of rows. Recently I have noticed that the newer tables take much longer (...
- 21
6
votes
1
answer
991
views
What is a valid use case for using TIME WITH TIME ZONE?
Along the lines of this related question:
What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE
Are there any valid use cases for actually using TIME WITH TIME ZONE or should it be ...
- 163
8
votes
3
answers
8k
views
Join 2 tables by closest time, PostgreSQL 9.6
I have 2 tables: tbl1, tbl2.
CREATE TABLE tbl1(time_1)
AS VALUES
( '2017-09-06 15:26:03'::timestamp ),
( '2017-09-06 15:26:02' ),
( '2017-09-06 15:28:01' ),
( '2017-09-06 15:40:00' );
CREATE ...
- 261
5
votes
1
answer
2k
views
May postgresql's uuid_generate_v1() conflict when we change the computer date/time?
According to postgresql uuid-ossp documentation uuid_generate_v1() is based on Mac address + timestamp:
https://www.postgresql.org/docs/9.4/static/uuid-ossp.html
On a distributed database scenario ...
- 467
1
vote
2
answers
6k
views
Compare an hour between two hour columns
Please help. I am kinda new to this database world. I am using PostgreSQL.
I have a table with defined shifts like this:
CREATE TEMPORARY TABLE shifts AS
SELECT id, start_hour::time, end_hour::time
...
- 11
16
votes
4
answers
20k
views
How do I generate a time series in PostgreSQL?
If you're looking to generate a date series, see this question
Let's say I want to generate a series for every 5 minutes for 24 hours. How do I do that in PostgreSQL?
PostgreSQL can generate_series()...
- 64.7k
8
votes
4
answers
8k
views
PostgreSQL interval division
This has come up a couple of times, e.g., in the postgresql newsgroup and the wiki. In general, the relationship between different intervals may not be well defined - a month can be different numbers ...
- 1,730
2
votes
2
answers
2k
views
Does PostgreSQL Explain Analyze Execution Time consider Fetching time
I have some tests on GitHub which record how much time it takes to execute a query and fetch all results.
If I check the execution plan, I get:
Index Scan using post_comment_pkey on post_comment ...
- 907
2
votes
1
answer
384
views
Grouping data by year based on two timestamp columns
My data table has the following columns:
id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP
I want to product a report of the count of each name (which can appear several times in the table)...
- 23
0
votes
1
answer
1k
views
Create interval by string in PostgreSQL
Is it possible to create an interval by string in PostgreSQL?
for example, I have a table:
CREATE TABLE x (
id BIGSERIAL PRIMARY KEY,
data JSONB
);
INSERT INTO x(data) VALUES('{"day_sess":"4_1"}');...
- 1,373
2
votes
1
answer
551
views
Mixed granularity weeks/months time series data in a data warehouse?
I'm not sure that "anti-aliasing of time series data" is the correct terminology to use, so let me explain:
I have some sources of data that are aligned quarterly, mostly to do with quarterly running ...
- 243
8
votes
1
answer
6k
views
Time dimension or timestamp in fact table?
Which would you use, and why? A separate time dimension or putting a timestamp in a fact table? Or perhaps both?
I am building a data warehouse, and need to represent the time of day that events ...
- 243
3
votes
1
answer
3k
views
Calculate Difference in Overlapping Time in PostgreSQL / SSRS
I'm using PostgreSQL 9.2 and SSRS 3.0. I'm trying to calculate the difference in non-overlapping time entries in a timesheet.
My data looks like this:
I would like this to return:
I can get the ...
- 31
-1
votes
1
answer
5k
views
Get currentmilliseconds in postgres
I'm working on timestamp/datetime in my application. I'm using postgres db.
What is the way to get current milliseconds (something like 1412706599000) in postgres?
- 39
11
votes
6
answers
22k
views
Efficiently storing irregular/repeating intervals (think calendar/events)
I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing.
Right now I'...
- 121