Questions tagged [time]
The time tag has no usage guidance.
94 questions
8
votes
6
answers
3k
views
How can we prevent Agent Jobs running twice when the clocks change?
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 ...
- 90.3k
1
vote
1
answer
817
views
How to reduce redundant time-series data in MySQL into a function?
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 ...
CommunityBot
- 1
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
-----------------------------------------------------------------------------------------------------...
Nuh Jama
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
3
votes
2
answers
299
views
How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?
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 ...
- 328
0
votes
0
answers
95
views
Keep both non-overlapping intervals and overlapping ones with a larger id MySQL
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....
- 101
2
votes
2
answers
6k
views
Can we set LOCK_TIMEOUT instance- or database-wide?
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/...
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 ...
- 182k
0
votes
2
answers
60
views
How I do I aggregate or perform calculations on a time datatype in "MM:SS:MS" in MySQL?
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 ...
- 79.4k
0
votes
1
answer
145
views
create_time in sys.dm_xe_sessions is in the future
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
+-------------------------------+...
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
0
votes
1
answer
90
views
Why does MariaDB execution time is doubled for the same query (LOAD DATA INFILE)?
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 ...
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 ...
1
vote
0
answers
407
views
Resample time series in SQLite
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),
(...
- 113
57
votes
6
answers
71k
views
How to combine date and time to datetime2 in SQL Server?
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 ...
- 90.3k
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 ...
- 503
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:
...
-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?
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
0
votes
1
answer
92
views
Do we have information about duration time of query in actual execution plan?
I am building application in C# which is parsing Actual Execution Plan. I need basic stats like Row count, CPU time, Page Reads etc. All above stats I found in XML of Actual Execution Plan. I can not ...
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()...
- 182k
5
votes
1
answer
775
views
Standard Deviation for Times
Is there a T-SQL coding best practice to get an accurate standard deviation value for a group of times? STDEV doesn't like the time data type. I'm thinking that maybe a conversion to minutes as an ...
- 90.3k
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:
...
CommunityBot
- 1
8
votes
2
answers
4k
views
How to get non-overlapping distinct intervals from a PostgreSQL table?
Using postgresql 9.6.
The table has user sessions and I need distinct non overlapping sessions printed.
CREATE TABLE SESSIONS(
id serial NOT NULL PRIMARY KEY,
ctn INT NOT NULL,...
- 11
2
votes
1
answer
2k
views
SSIS - converting string to HH:MM:SS
I am trying to read flat file with some data and I need to convert some columns to time type in the format of HH:MM:SS.
Below is the example how they are stored in the file (on the left) and how I ...
CommunityBot
- 1
6
votes
3
answers
1k
views
Store time series data efficiently without wasting space
I'm building an application to calculate different KPI metrics for customers of an ecommerce website e.g. (avg. order value, avg. items count and so on). KPIs are integer and or double values e.g. ...
- 79.4k
0
votes
1
answer
67
views
How to design database for monitoring the time trends of article tags?
My purpose is to monitor which tags increase/decrease rapidly last week on different bulletin boards. Or which tag do new appear. The current table schema of articles is as follows:
article_id e.g. ...
- 2,048
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 ...
- 182k
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 ...
- 3,412
0
votes
1
answer
50
views
WITH-clause not pre-calculated, lineal resource wasting, why?
I'm trying to save resources, and shorten code, but getting lineal resource wasting with "WITH-clause".
This is my base example:
#!/bin/bash
echo "CREATE TABLE 'clients_table' ( id ...
- 2,083
1
vote
1
answer
4k
views
Calculate time difference in minutes
I am Amazon Redshift. I tried to calculate the time difference between two time stamp columns in minutes, but the timestampdiff function does not work on it. The error is telling me that minute column ...
- 70.5k
0
votes
1
answer
126
views
Same server, same query, different response time
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 ;
* ...
- 79.4k
-2
votes
1
answer
53
views
Function to convert time difference between two dates into timer format
Does anyone have a function that converts date difference between two dates into timer format ?
For example:
select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 14:12:35')
The result should ...
- 16.5k
0
votes
1
answer
7k
views
Querying a date range
i query this SQL query:
SELECT * FROM [DB].[dbo].[Table]
WHERE [DATE] BETWEEN '01-01-2016' AND '31-03-2016'
AND ([TIME] >= '00:00:00' OR [DATE] > '01-01-2016')
AND ([TIME] <= '00:00:00' OR [...
- 182k
2
votes
1
answer
1k
views
Same query on same same table, different response time
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 ;
* ...
- 583
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. ...
- 56.4k
1
vote
1
answer
1k
views
Align values to 5 minute intervals
I have values inserted with random intervals in the table. I would like to fetch the first (if exists) value in (say) 5 minutes interval. So is required to have 5 minutes column and the values column ...
- 20.3k
0
votes
2
answers
5k
views
Operand data type time is invalid for add or subtract operators
I'll to calculate how many hours a person has worked on SQL server. For that I'm storing the Arrival, Departure and Break in my database table as time(7). A bit for DepartureNextDay determinate that ...
CommunityBot
- 1
-1
votes
2
answers
174
views
Refining Productivity & Efficiency Query
I'm trying to write a query that is, in effect, a Productivity and Efficiency Report for measuring the statistics of those of us (such as myself) who type up the paper work orders that our mechanics ...
CommunityBot
- 1
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
46
views
Lock database in time
I'm currently working with prototyping some stuff with a dump of a MySQL (5.7) database where much of the data is time dependent. Most of the queries I write use the NOW() function and the results ...
- 70.5k
0
votes
1
answer
162
views
time difference between two machines makes problem in service broker communication
I have two machines and each machine has a SQL Server instance. Between these machines I run service broker. One of my machines has a different time from the other. I mean the machine time of A is 9:...
- 25.1k
0
votes
1
answer
140
views
Calculating the elapsed time of Oracle Database Startup operation
I'm trying to calculate the elapsed time of Oracle Database startup command. The view V$INSTANCE contains the STARTUP_TIME column which tells at what time the database is started. But, i need way to ...
- 41.1k
2
votes
1
answer
189
views
Sum values by time
id_ | date_ | hour_ | in_|out_|
|…. | …. | …. | …. | ….
|19 | 2018-12-12| 18:15:00| 1 | 3 |
|19 | 2018-12-12| 18:30:00| 1 | 0 |
|19 | 2018-12-12| 18:45:00| 2 | 4 |
|...
- 13.2k
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
...
CommunityBot
- 1
-2
votes
1
answer
827
views
Database design for a timesheet app?
I'm designing the database schema for a time tracking application and I need a little piece of advice. The application must permit the user:
To enter for each day of the week the amount of time he ...
2
votes
1
answer
4k
views
datetime row created
I have sqlserver DB , and there I have few tables.
I have 1 table that not save each row what is the datetime the each row created.
Is there any way to know when which row was created?
Is there any ...
- 3,306
1
vote
1
answer
3k
views
Date and time period query
I would be very grateful if someone can help me here: in my database, I have prices recorded at a particular time in a day over years. So for each vendor or station, there can be several price data ...
- 79.4k
0
votes
1
answer
432
views
MariaDB query time in profile not match
From a MariaDB 10.1 database with the profile enabled.
After making some test with an example query, why the time reports from the query doesn't match the duration time that the profiles report?
I ...
- 26.9k
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'...
CommunityBot
- 1