Questions tagged [time]
The time tag has no usage guidance.
1
vote
1answer
19 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 ...
3
votes
1answer
30 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 ...
0
votes
2answers
41 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 ...
0
votes
1answer
28 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:...
0
votes
1answer
21 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 ...
2
votes
1answer
47 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 |
|...
-2
votes
1answer
102 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 ...
1
vote
1answer
29 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 ...
0
votes
1answer
371 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 ...
1
vote
1answer
130 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 ...
0
votes
1answer
136 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 ...
2
votes
1answer
581 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 ...
3
votes
2answers
678 views
How should I represent a midnight end time?
I have a table which represents time ranges.
Column | Type | Modifiers
-------------+----------------------...
2
votes
0answers
64 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 (...
2
votes
1answer
67 views
Select where next row's value is greater
I have a road_insp table.
create table road_insp
(
insp_id int,
road_id int,
insp_date date,
condition number(38,2)
) ;
insert into road_insp (insp_id, road_id, ...
5
votes
1answer
277 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 ...
3
votes
3answers
2k 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 ...
0
votes
2answers
151 views
How to add index to increase speed of mysql statement
How do I increase the speed of this mysql statement? taking 1.10s but was wondering if it could be made faster.
SELECT
res_rooms.room_id AS room,
res_rooms.type AS room_type,
...
4
votes
1answer
505 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 ...
3
votes
1answer
247 views
Position of a race result using database query?
The scenario is based around a race event (running/jogging)
Is there a way of getting a position of a person by using their race results. For example the quickest person to finish a race would be in ...
1
vote
2answers
138 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 ...
0
votes
2answers
883 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
...
1
vote
2answers
214 views
SQL to choose a price between two points in time
Given a time-series of datetime price changes and another disparate time-series of N events, how do I query the last known price at the time of each event without running N queries? Essentially, I ...
8
votes
4answers
3k 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()...
0
votes
1answer
28 views
How to create a CSV file which imports data from an Access Database on a Weekly Schedule?
I have very little experience in database management but have come across a problem I am unable to find a solution to online. I currently have access to a database which is updated in real time with ...
2
votes
2answers
1k 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 ...
0
votes
2answers
2k views
Clock change on SQL Server
With the clock change ahead in Europe - how does SQL Server deal with the clock change? Are there known issues related to a clock change?
0
votes
1answer
305 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 ...
0
votes
1answer
620 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
4answers
1k views
Update an entry in table when time expires
I need to modify an entry in table, based on time.
i.e. table contains customer-info & subscription-info. I need to modify/delete subscription when subscription expires for each user.
There can ...
0
votes
1answer
46 views
Can I get an equivalent query for this Not in Query
SELECT ID FROM dbo.SomeTable
WHERE ID not in
(
SELECT MIN(ID)
FROM dbo.SomeTable
GROUP BY SystemNo
)
As this query ...
1
vote
0answers
143 views
Is there a faster way of importing 8m+ rows?
I'm creating a new feature on my website in order to provide player locations (my website is for a multiplayer gaming service), and am going to use a simple IP lookup in my database.
For this I need ...
0
votes
1answer
1k 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 [...
1
vote
0answers
94 views
Time series data and utc conversion
I'm storing data in minute, hour, day, month and year buckets. All data is stored in UTC. Let's assume a client is in PDT time (-07:00 UTC)
If the client wants to query for the hour sum of 4/23/2016 ...
0
votes
1answer
2k views
MS Access Data Type for time in hh:mm:ss format
My database contains three different sporting events with finishing times imported from an Excel spreadsheet in the hh:mm:ss format.
How do I customize the Access data type field to accept this ...
2
votes
1answer
104 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)...
3
votes
1answer
887 views
Constraint to prevent overlapping Time periods
I have a pretty simple table that stores a StartTime and an EndTime, both with the data type of Time(0). I need to enforce a constraint that prevents any overlapping. 'Touching' time periods are OK, ...
2
votes
1answer
254 views
Calculate hours based on a course schedule in mysql
I am trying to calculate user's attendance hours based on a course schedule.
The user enters a room and I'm tracking in & out time.
So I have the following tables
user_id | room_id | ...
4
votes
2answers
2k views
MySQL: sum time ranges exluding overlapping ones
I need to sum up the time which results from multiple time ranges. For example - we have enter / exit ranges of some office:
Query has to:
Exclude overlapping ranges (8:00 - 10:00)
Exclude "missing" ...
1
vote
0answers
29 views
Heirarchial Database with Time Tracking
Working in Python/Django 1.8
I have a problem along the following lines:
Assembly Table
|Part
|Parent (FK to other Part)
i.e.:
Car
|--Tire
|--Bolt A
|--Bolt B
Journey Table
|Date
|...
0
votes
1answer
310 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
vote
2answers
66 views
Define a db before coding
I'm starting to code time tracking application to replace the monthly excel sheets my company is requiring. Its purpose is standard : to centralize all data about the time spent by different(or same) ...
0
votes
1answer
54 views
Time Required For Query Execution?
I am new to working with databases and am unaware of their capacity in general.
To be specific, I want to know much much time would a particular mysql query take. I tried searching on the internet but ...
0
votes
2answers
2k views
Getting 30 minute slots between time
I have a Start Datetime and a End Datetime.
Eg: 10:00 am - 12:00 pm
I have to create 4 slots between the, each ranging for 30 minutes.
Eg:
10:00 am-10:30 am
10:30 am-11:00 am
11:00 am-11:30 am
...
1
vote
1answer
352 views
Extract time from date into new column
I have a varchar column named [Dato Time] which contains values like:
2011-05-12 00
2011-05-12 01
2011-05-12 02
So, a date and a Hour-specific time.
I would like to split the date and the time up ...
2
votes
1answer
280 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 ...
6
votes
1answer
2k 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 ...
2
votes
1answer
989 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 ...
3
votes
1answer
817 views
Any risks in updating the system time on a mysql 5.5 server?
We just discovered one of our mysql systems has not been running ntp, with the result that the time has drifted a bit.
If I enable ntp, and synchronize the time while the system is running are ...
1
vote
2answers
188 views
Selecting the average of the top percent of duration, grouped by minute
I am attempting to find the average of (the top 5% of duration/ per minute).
I wrote this query:
select AVG(Q.duration) Avg5Perc, Q.time FROM
(
select top 5 PERCENT duration, time from ...
