Questions tagged [time]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
0 answers
24 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....
folow's user avatar
  • 339
0 votes
0 answers
38 views

MySQL time field type conversion issue with AWS DMS

I have a DB MySQL v5.6 community edition as a source DB and AWS RDS MySQL v5.7 as target in AWS DMS and have an issue with time field type. DMS task is full load and replication. During full load all ...
blagi's user avatar
  • 1
3 votes
2 answers
176 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 ...
J.D.'s user avatar
  • 37.1k
0 votes
0 answers
38 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....
Alex.Kh's user avatar
  • 101
0 votes
2 answers
40 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 ...
thisisforfun's user avatar
0 votes
1 answer
61 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 ...
GiorgioAbitbolo's user avatar
0 votes
1 answer
92 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 ...
Stavros Koureas's user avatar
1 vote
0 answers
316 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), (...
adius's user avatar
  • 113
1 vote
0 answers
42 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: ...
Chris Stryczynski's user avatar
0 votes
0 answers
19 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 ...
Ουιλιαμ Αρκευα's user avatar
0 votes
1 answer
86 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 ...
grochu's user avatar
  • 45
5 votes
1 answer
691 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 ...
AccidentalDBA_CO's user avatar
1 vote
1 answer
38 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: ...
EdenSource's user avatar
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. ...
cardy's user avatar
  • 63
0 votes
1 answer
65 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. ...
Planetoid Hsu's user avatar
1 vote
1 answer
355 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 ...
Tomus85's user avatar
  • 13
1 vote
1 answer
509 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 ...
Chessbrain's user avatar
  • 1,183
0 votes
1 answer
47 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 ...
ktaqzyvp's user avatar
1 vote
1 answer
3k 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 ...
Tracy Liu's user avatar
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,...
ERJAN's user avatar
  • 465
-2 votes
1 answer
46 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 ...
Aleksey Vitsko's user avatar
0 votes
1 answer
102 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 ; * ...
Elleby's user avatar
  • 21
2 votes
1 answer
932 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 ; * ...
Elleby's user avatar
  • 21
0 votes
1 answer
42 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. ...
Jason's user avatar
  • 153
0 votes
1 answer
114 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 +-------------------------------+...
SE1986's user avatar
  • 1,950
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 ...
edo's user avatar
  • 25
1 vote
1 answer
46 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 ...
Radio Active's user avatar
3 votes
1 answer
44 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 ...
Johan Falkenjack's user avatar
0 votes
2 answers
4k 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 ...
H. Pauwelyn's user avatar
0 votes
1 answer
144 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:...
Ehsan Akbar's user avatar
0 votes
1 answer
114 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 ...
bprasanna's user avatar
  • 123
2 votes
1 answer
133 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 | |...
JBZ's user avatar
  • 23
-2 votes
1 answer
778 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 ...
Zakaria Naji's user avatar
2 votes
1 answer
3k 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 ...
sqlServer5555's user avatar
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 ...
Krzysztof Piotrowski's user avatar
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 ...
aoe207's user avatar
  • 13
0 votes
1 answer
415 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 ...
rfmoz's user avatar
  • 101
3 votes
1 answer
24k 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 ...
Harshal Mahajan's user avatar
3 votes
2 answers
9k views

How should I represent a midnight end time?

I have a table which represents time ranges. Column | Type | Modifiers -------------+----------------------...
John Bachir's user avatar
2 votes
0 answers
133 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 (...
Kat's user avatar
  • 21
2 votes
1 answer
108 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, ...
User1974's user avatar
  • 1,457
5 votes
1 answer
910 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 ...
Eduardo's user avatar
  • 153
7 votes
3 answers
7k 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 ...
delkov's user avatar
  • 251
0 votes
2 answers
229 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, ...
apex's user avatar
  • 75
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 ...
Thiago Sayão's user avatar
2 votes
1 answer
715 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 ...
HeadsR'Us's user avatar
-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 ...
Ben Williamson's user avatar
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 ...
Andy's user avatar
  • 11
1 vote
2 answers
2k 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 ...
Petrus Theron's user avatar
16 votes
4 answers
19k 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()...
Evan Carroll's user avatar
  • 62.7k