Questions tagged [time]
95 questions
57
votes
6 answers
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 23:59:59.9999999'?
Some things which don't work are…
Martin Smith
- 87,941
- 15
- 255
- 354
16
votes
4 answers
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() from a timestamp, but not from time.
Is it better…
Evan Carroll
- 65,432
- 50
- 254
- 507
11
votes
6 answers
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'm storing them in a postgres database, but I have…
MadsRC
- 121
- 1
- 1
- 5
9
votes
3 answers
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 TABLE tbl2(time_2)
AS VALUES
( '2017-09-06…
delkov
- 271
- 2
- 6
8
votes
6 answers
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 go back an hour, SQL Server will repeat any…
J. Mini
- 1,161
- 8
- 32
8
votes
2 answers
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,
day DATE NOT NULL,
…
ERJAN
- 483
- 5
- 7
8
votes
4 answers
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 of days depending upon which month (and year) is…
beldaz
- 1,740
- 3
- 16
- 26
8
votes
1 answer
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 occur at, down to the one second granularity. I want…
user2800708
- 243
- 2
- 5
6
votes
3 answers
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. number of items bought, avg. order value, gross…
cardy
- 63
- 5
6
votes
1 answer
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 considered an anti-pattern?
To be clear: I am asking…
Eduardo
- 163
- 7
5
votes
1 answer
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 integer, but a conversion from/to what? Any…
AccidentalDBA_CO
- 157
- 1
- 7
5
votes
2 answers
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 where we have hundreds of databases generating…
Thiago Sayão
- 487
- 8
- 16
5
votes
2 answers
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" part (12:00 - 14:00)
Expected result in this…
Łukasz
- 83
- 1
- 6
4
votes
1 answer
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, like 2pm-3pm and 3pm-4pm. I have created a…
koolaide
- 43
- 1
- 4
4
votes
1 answer
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 there any possible problems? The master is currently…
chris
- 1,232
- 5
- 17
- 29