All Questions
Tagged with time postgresql
27 questions
0
votes
1
answer
30
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
75
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....
- 513
0
votes
1
answer
122
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
68
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
44
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
443
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
651
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
52
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. ...
- 163
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
27k
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
1k
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
9
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 ...
- 271