PostgreSQL displays zero time in a strange way for non-UTC time zones. The difference in time zones is in minutes and even seconds. What is the reason for this?

SET TIME ZONE 'UTC';
select '0001-01-01T00:00:00Z'::timestamptz;
      timestamptz       
------------------------
 0001-01-01 00:00:00+00

SET TIME ZONE 'Europe/London';
select '0001-01-01T00:00:00Z'::timestamptz;
           timestamptz           
---------------------------------
 0001-12-31 23:58:45-00:01:15 BC

SET TIME ZONE 'Australia/Melbourne';
select '0001-01-01T00:00:00Z'::timestamptz;
         timestamptz          
------------------------------
 0001-01-01 09:39:52+09:39:52

I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes. The setup is: * virtual server; * 4 virtual CPU; * 8 GB of virtual memory ; * 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available; * OS Debian 9; * Mysql 5.6.47; To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed. I had enabled the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE MALICIOUS_TABLE (
  column_1 int(11) NOT NULL AUTO_INCREMENT,
  column_2 varchar(8) NOT NULL,
  column_3 datetime NOT NULL,
  column_4 int(11) NOT NULL,
  column_5 int(11) DEFAULT NULL,
  column_6 int(11) DEFAULT NULL,
  column_7 int(11) DEFAULT NULL,
  column_8 tinyint(1) DEFAULT NULL,
  column_9 datetime DEFAULT NULL,
  column_10 int(11) DEFAULT NULL,
  column_11 varchar(2048) DEFAULT 'column_11',
  column_12 tinyint(1) DEFAULT NULL,
  column_13 datetime DEFAULT NULL,
  column_14 tinyint(1) DEFAULT NULL,
  PRIMARY KEY (column_1),
  KEY fk_ual_aut_idx (column_2),
  KEY fk_aul_c_idx (column_4),
  KEY kf_ual_po_idx (column_5),
  KEY fk_ual_ute_idx (column_10),
  KEY column_1 (column_1),
  KEY column_2 (column_2),
  CONSTRAINT fk_aul_c FOREIGN KEY (column_4) REFERENCES t_table2 (column_4) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_aut FOREIGN KEY (column_2) REFERENCES t_tabl3 (column_2) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_po FOREIGN KEY (column_5) REFERENCES t_table4 (column_5) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT fk_ual_ute FOREIGN KEY (column_10) REFERENCES t_table5 (column_10) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from MALICIOUS_TABLE;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM MALICIOUS_TABLE WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time. The RAM of the server it's good, still getting about 2 giga of free RAM. The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no. Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself? Thank you in advance.

i have a question that actually does not fit here very much, but maybe it fits. So, i have data like this: How can i reduce this 3 entries with same values? Sure, i can just delete the other 2, but in time-series data i can not just do this, this would hide the information, that in between the real time 15:19:45 and 15:19:55, the value did not change. So i thought about saving the delta-time and value in a separate table and only save the first entry and mark it, but i dont know if this is the best way to do so.

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 where we have hundreds of databases generating records with UUID keys and syncing back to a central database. Suppose we detect a machine has a wrong date/time in the future and we change it back to the correct date/time. May it generate a conflicted UUID key on this particular machine? One scenario is the summer time / daylight savings.

How is it possible to convert a float number of seconds to HH:MM:SS in Informix? I have a column that has a run duration of 1449.448520410. I want to convert this to a human-readable format. I have identified that running the below gives close to what I want, but excludes the hours:

select b.run_duration, 
       floor(run_duration / 60) || ':' || lpad(mod(run_duration, 60), 2, '0') as run_duration_time 
from ph_task a, ph_run b 
where a.tk_id = b.run_task_id 
order by run_duration DESC

Output:

-none
24:09

What I would like to see is:

-none
00:24:09

How can I customize my SQL to provide that?

## Context I have no idea where this is documented and was shocked that I could not find a duplicate. I only know it from seeing it go wrong several years in a row. ## The Problem On the day when the clocks go back an hour, SQL Server will repeat any Agent Jobs that run at a fixed time each day. This means that if I have a job run at 01:05 each day, then it will run twice in the same day if the clocks go back an hour at 02:00. Is there any means within SQL Server Agent to prevent this? [It appears that AWS EventBridge Schedules can do it](https://docs.aws.amazon.com/scheduler/latest/UserGuide/schedule-types.html#daylist-savings-time) . The Q & A https://dba.stackexchange.com/questions/189663/daylight-saving-time is related, but their jobs run on a regular frequency (once every 15 minutes). Mine runs at a specific time. The problem has been witnessed several years in a row across more than one server.

QUERY PLAN                                                                                        

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GroupAggregate  (cost=21267.11..21286.98 rows=461 width=31) (actual time=1.711..1.712 rows=1 loops=1)
Group Key: *
\-\>  Sort  (cost=21267.11..21268.91 rows=719 width=35) (actual time=1.564..1.591 rows=719 loops=1)
Sort Key: *
Sort Method: quicksort  Memory: 69kB
\-\>  Nested Loop  (cost=70.03..21233.00 rows=719 width=35) (actual time=0.483..1.454 rows=719 loops=1)
\-\>  Index Scan using *  (cost=0.28..8.30 rows=1 width=27) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: *
\-\>  Bitmap Heap Scan on measurements m  (cost=69.75..21213.91 rows=719 width=32) (actual time=0.240..0.994 rows=719 loops=1)
Recheck Cond: *
Filter: *
Rows Removed by Filter: 5241
Heap Blocks: exact=50
\-\>  Bitmap Index Scan on * (cost=0.00..69.57 rows=6018 width=0) (actual time=0.224..0.224 rows=5960 loops=1)
Index Cond: *
Planning Time: 0.697 ms
**Execution Time: 1.766 ms**
(17 rows)

                                              QUERY PLAN                                                                                        

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GroupAggregate  (cost=21267.11..21286.98 rows=461 width=31) (actual time=0.897..0.898 rows=1 loops=1)
Group Key: *
\-\>  Sort  (cost=21267.11..21268.91 rows=719 width=35) (actual time=0.795..0.831 rows=719 loops=1)
Sort Key: *
Sort Method: quicksort  Memory: 69kB
\-\>  Nested Loop  (cost=70.03..21233.00 rows=719 width=35) (actual time=0.178..0.718 rows=719 loops=1)
\-\>  Index Scan using * (cost=0.28..8.30 rows=1 width=27) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: *
\-\>  Bitmap Heap Scan on measurements m  (cost=69.75..21213.91 rows=719 width=32) (actual time=0.081..0.457 rows=719 loops=1)
Recheck Cond: *
Filter: *
Rows Removed by Filter: 5241
Heap Blocks: exact=50
\-\>  Bitmap Index Scan on * (cost=0.00..69.57 rows=6018 width=0) (actual time=0.073..0.073 rows=5960 loops=1)
Index Cond: * 
Planning Time: 0.336 ms
**Execution Time: 0.929 ms**
(17 rows)

                                              QUERY PLAN                                                                                        

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GroupAggregate  (cost=21267.11..21286.98 rows=461 width=31) (actual time=0.873..0.873 rows=1 loops=1)
Group Key: *
\-\>  Sort  (cost=21267.11..21268.91 rows=719 width=35) (actual time=0.794..0.813 rows=719 loops=1)
Sort Key: *
Sort Method: quicksort  Memory: 69kB
\-\>  Nested Loop  (cost=70.03..21233.00 rows=719 width=35) (actual time=0.168..0.717 rows=719 loops=1)
\-\>  Index Scan using * (cost=0.28..8.30 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=1)
Index Cond: *
\-\>  Bitmap Heap Scan on measurements m  (cost=69.75..21213.91 rows=719 width=32) (actual time=0.071..0.457 rows=719 loops=1)
Recheck Cond: * 
Filter: *
Rows Removed by Filter: 5241
Heap Blocks: exact=50
\-\>  Bitmap Index Scan on * 
(cost=0.00..69.57 rows=6018 width=0) (actual time=0.063..0.063 rows=5960 loops=1)
Index Cond: *
Planning Time: 0.304 ms
**Execution Time: 0.903 ms**
(17 rows)

\---------------------------------------------------------------------------------------------------------------

Time (mean ± σ):      **98.1 ms** ±  28.1 ms    \[User: 30.7 ms, System: 11.1 ms\]Range (min … max):    75.6 ms … 129.5 ms    3 runs

\---------------------------------------------------------------------------------------------------------------

I'm using hyperfine to benchmark the performance of my query in PostgreSQL. I used --runs 3 option to run it three times. As you can see, the execution time for all the three times I'm running the query takes 1.766, 0.929 and 0.903 ms respectively. My question is, why is the mean = 98.1 ms? What does this mean represent? Because it does not make any sense that the execution time is between 0.9 ms - 1.7 ms, while the mean of them is 98.1 ms. I tested to execute this same query in Postico and it took 0.903 ms. I'm just curious what the mean represents if it does not represent the execution average time.

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.pg_stat_all_indexes.last_idx_scan* - records last index scan I would like to get last table access time, so when is the last time: select, update, delete or insert was executed on table and I am interested if something got easier for PostgreSQL v16. I executed: - create table public.tab1 (id integer not null primary key); - insert into public.tab1 values (1); - select id from public.tab1 where id = 1; - update public.tab1 set id = 2 where id = 1; - delete from public.tab1 values where id = 2; After every above SQL I have executed bellow SQL to get last table access time: select table_name, max(last_time) as last_access from ( select schemaname || '.' || relname as table_name, last_seq_scan as last_access_time from pg_catalog.pg_stat_all_tables union all select schemaname || '.' || relname as table_name, last_idx_scan as last_access_time from pg_catalog.pg_stat_all_indexes ) as mytable where table_name = 'public.tab1' group by table_name ; I get new time in last_access_time for every action above except "insert". If I do the explain of above commands I see "sequential scan" or "index scan" is NOT performed for "insert" statement, but it is performed for: select, update or delete. I already know for other pre-PostgreSQL v16 solutions like: - create trigger on insert - create table with column: *last_insert default now()* - turn on "track_commit_timestamp" and access "pg_xact_commit_timestamp(xmin)" My question, I am specially interested in two things: - Is there any solution without changing any objects/settings in PostgreSQL just pure select statement **and** - is there something new related to this in PostgreSQL v16.

I have the following EventTimes table: DROP TABLE IF EXISTS dbo.EventTimes; CREATE TABLE dbo.EventTimes ( EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY, EventId INT NOT NULL, StartTime TIME NOT NULL, EndTime TIME NOT NULL ); With the following data: -- Event 1 INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime) VALUES (1, '04:00:00', '14:00:00'), (1, '06:00:00', '11:00:00'), (1, '09:00:00', '12:00:00'), (1, '13:00:00', '14:00:00'), -- Gap between this row and the next row (1, '02:30:00', '04:00:00'); -- Notice the half-hour on this one -- Event 2 INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime) VALUES (2, '00:00:00', '06:00:00'), -- Gap between this row and the next row (2, '09:00:00', '13:00:00'), (2, '11:00:00', '15:00:00'); Notice: - The same Event can have two time ranges that overlap each other. E.g. same Event from 4 AM to 2 PM and and also from 6 AM to 11 AM. - There can also be gaps between the two time ranges. E.g. same Event from 1 PM to 2 PM and also from 2:30 PM to 4 PM. End Goal: I'm trying to calculate the TotalTime of a given Event ignoring the duplicate overlapping time. E.g. for the set of ranges of 9 AM to 1 PM and 11 AM to 3 PM, the distinct TotalTime should be 6 hours (9 AM to 3 PM). Conversely, I also don't want to count the time in the gaps between two time ranges. So for the set of ranges of 1 PM to 2 PM and 2:30 PM to 4 PM the TotalTime should be 2.5 hours. (Note these are just subsets of the full example above, and the final result should be the sum of all of these unique times per Event.) The TotalTime should never exceed 24 hours, these times are all within a single day (as is the TIME data type). Expected final results for the provided examples in the scripts above: dbfiddle.uk for reference. --- Other Info: - If it's easier to work with datetimes, feel free to alter the data types from TIME to DATETIME. I can convert the results back, no problem. - I believe some sort of recursion is needed to solve this. I feel I'm pretty close on a solution, but not quite there yet. In a case such as 2:30 - 4:00 and 4:00 - 14:00, I'd expect the Total Time to be 11.5 hours.

I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique. start, end are timestamps(and end can be NULL). Say that I want to eliminate overlapping intervals but if there's any, I want to keep the ones with the highest interval_id. For the interval elimination, I am using the following criteria:

(t1.start =t2.start OR t1.end IS NULL)

I am doing a (self) CROSS JOIN and have something like this already:

SELECT t1.object_id, t1.interval_id, t1.start, t1.stop
FROM data AS t1, data AS t2
WHERE t1.object_id = t2.object_id -- we want to compare same objects
AND (t1.start =t2.start OR t1.end IS NULL)) --overlapping condition
AND t1.interval_id > t2.interval_id --keep ones with the highest id?

My main question is if this is a correct approach to keep both non-overlapping intervals and overlapping intervals with the highest interval_id or am I doing anything wrong? I wish I could fully test out myself but I currently don't have access to the db, so any input would be appreciated.

It is possible to change the SQL Server's default (which is -1 or indefinite period of time) LOCK_TIMEOUT value session-wide. Is it possible to set LOCK_TIMEOUT default value server-, instance- and/or database-wide? If not, why not?

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 overlapping minutes 635, but am having trouble with the not overlapping, 605 minutes.

I'm currently working on an NBA project and I need to take the average minutes played per player, but I have no idea how to aggregate or calculate the time datatype. I know you have to convert it, but I have no idea what to covert it to. For example: a value is 31:56:00, but when I use calculations on it, it becomes 315600 as an integer, instead of remaining as that datatype with minutes, seconds, milliseconds. I apologize if this has been answered, but I can't find any solutions online and I'm still learning the fundamentals of MySQL. Edit: Here's a screenshot of the data and I'll show what happens if I perform a calculation or an aggregate, it'll basically remove the colons and keep it as an integer, but I want it to remain in the same format.

I have run the following query which shows the running XE sessions on the server: SELECT name, create_time FROM sys.dm_xe_sessions and I get the results +-------------------------------+-------------------------+ | name | create_time | +-------------------------------+-------------------------+ | hkenginexesession | 2020-01-04 06:39:14.240 | | system_health | 2020-01-04 06:39:14.313 | | Deadlocks | 2020-01-04 06:39:14.483 | | sp_server_diagnostics session | 2020-01-04 06:39:14.583 | | Blocked Processes | 2019-11-20 14:27:49.593 | +-------------------------------+-------------------------+ Note that the first four sessions, 3 of which are system, 1 of which is user created (Deadlocks) have a create_time in the future. I can verify the system time is correct doing a simple SELECT GETDATE() The Blocked Processes xe session has been created since the last server restart (which was 2019-11-15 13:36:35.287) What could have caused this? SQL Server Version is Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64) Jul 20 2019 21:42:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Latest months I am working with PostgreSQL while coming from Microsoft world (SQLServer). My scenario is the following: 1. Transfer data from different databases into single one (same set of tables and structure) 2. Perform lightweight transformations (some simple joins, groups, sums and divisions) The first part of transferring in real-time between PostgreSQL databases can be achieved using Logical Replication, especially in PostgreSQL 15 where you can select subset of columns of the tables. For the second part there is the Incremental Materialized View extension for PostgreSQL, but this is currently supporting only Inner-Joins and does not work with Logical Replication, while having lot of other limitations. In addition is not yet implemented on Azure Managed Instances... Any ideas of how we can archive the second goal?

I observed a strange behaviour regarding the execution time of a query to import a CSV file in an empty table created beforehand. The query execution time to import the file increases while repeating the import. I meet this behaviour while importing 10 times the same medium CSV file (0.6 GB, 6 columns, 8 million rows) using TRUNCATE then LOAD DATA INFILE, repeated 10 times within one MariaDB connection. On the first iteration, the CSV import takes 40 seconds, then about 50 seconds on the second iteration, and from the third to the 10th iteration, the execution time reaches a plateau at 85 +/- 5 s. I performed the test twice : - on the mariadb shell (alias "mysql" on GNU Linux) - on python3 using mysql.connector And I get the same result, i.e. an execution time that doubles (see figure)... **• What could explain (or avoid) the execution time being doubled between the first and the third iteration ?** Steps to reproduce the behaviour : 1. Initiation : create (just once) the empty table with a generated primary key (idRow) : CREATE TABLE myTable (col1 VARCHAR(14), col2 VARCHAR(14), col3 VARCHAR(10), col4 VARCHAR(5), col5 VARCHAR(5), col6 VARCHAR(19), idRow INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (idRow)); 2. Repeat steps A. and B. 10 times and collect the execution time of step B. for each iteration : A. Empty the table using TRUNCATE : TRUNCATE TABLE myTable; B. Then import a 0.6 GB-large CSV file of 8 million rows and 6 columns : LOAD DATA INFILE "/myData/myFile.csv" INTO TABLE myTable FIELDS TERMINATED BY "," LINES TERMINATED BY "\n" IGNORE 1 ROWS (col1, col2, col3, col4, col5, col6) SET idRow=NULL; Any help to understand this phenomenon would be welcome, dont hesitate to ask for more info. *Why do I do this ? The goal is to build a procedure to measure robustly the statistics of the execution time of any query, and how much it fluctuates determines the number of iterations one needs to get a relevant sample size. I was surprised that any query could fluctuate of 100% in execution time.* Giorgio MariaDB server : - OS : Linux Mint 20 - mariadb version : 10.3.38-MariaDB-0ubuntu0.20.04.1-log - innodb version : 10.3.38 [update] I made other interesting observations : (i) : On the same OS session (i.e. no reboot) : closing the mariadb connection, or restarting the mariadb service (systemctl restart mariadb) does not prevent the 2nd iteration getting slower (50 to 87 s) than the first. (ii) : After rebooting the OS, the B query gets fast again (40 sec).

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 postcommen0_ (cost=0.29..2716.48 rows=34752 width=43) (actual time=6.767..14.352 rows=34880 loops=1) Filter: (post_id = 1) Rows Removed by Filter: 34880 Planning time: 0.659 ms Execution time: 14.840 ms So, the query is using an index and executing in 15 ms. However, on the Java side, after the ResultSet is fetched and processed, the time measurement indicates that it took 90 ms. The only explanation would be that the Explain Analyze doe snot consider the fetch time and fetching 34880 records takes 50-60 ms. Is this correct?

I have following table:

CREATE TABLE entries (
  utc TEXT,
  entry INTEGER
);

with following data:

INSERT INTO entries
  (utc, entry)
VALUES
  ('2022-10-01 01:54', 23),
  ('2022-10-04 02:40', 46),
  ('2022-10-09 16:20', 51),
  ('2022-10-10 04:05', 81),
  ('2022-10-16 12:26', 78);

How can I write a query to get the daily value (linear interpolated) like this:

utc              | entry
-----------------|------
2022-10-01 00:00 | …
2022-10-02 00:00 | …
2022-10-03 00:00 | …
…                | …

Here is a pre-filled dbfiddle for playing around: https://dbfiddle.uk/TCX9Rd5t

Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999' What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'? Some things which **don't** work are listed below. --- SELECT @D + @T > Operand data type date is invalid for add operator. --- SELECT CAST(@D AS DATETIME2(7)) + @T > Operand data type datetime2 is invalid for add operator. --- SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D) > The datediff function resulted in an overflow. The number of dateparts > separating two date/time instances is too large. Try to use datediff > with a less precise datepart. \* The overflow can be avoided in Azure SQL Database and SQL Server 2016, using DATEDIFF_BIG . --- SELECT CAST(@D AS DATETIME) + @T > The data types datetime and time are incompatible in the add operator. --- SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME) > Returns a result but loses precision 2013-10-13 23:59:59.997

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 of days depending upon which month (and year) is under consideration. But sometimes it is necessary to calculate how many intervals occur between two points in time, e.g. (simplified example): CREATE TABLE recordings(tstart timestamp, tend timestamp, interval ticklength); SELECT (tend - tstart) / ticklength AS numticks FROM recordings; This isn't allowed in PostgreSQL because it is division between two intervals, which wouldn't have well defined general behaviour for the above reasons. A workaround exists for when an interval can be converted to seconds, but what is the best way when this is not the case, e.g, when the interval is of the order of milliseconds?