Questions tagged [time]
84 questions
56
votes
5 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
- 77,689
- 15
- 224
- 316
13
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…
NO WAR WITH RUSSIA
- 54,954
- 34
- 200
- 411
9
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
- 101
- 1
- 1
- 3
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
- 455
- 3
- 7
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
- 4
6
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
- 241
- 2
- 6
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
- 81
- 4
5
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
- 153
- 6
5
votes
1 answer
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
- 385
- 3
- 14
5
votes
3 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,580
- 3
- 14
- 23
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
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,182
- 4
- 16
- 28
3
votes
2 answers
Which of the following pieces of SQL is most efficient
I need to add the date part of GetDate() to the time part of a stored DateTime.
Naively I thought I could do the following:
declare @testTime DateTime = '2013-04-23 13:55:06'
select cast(getdate() as date) + cast(@testTime as time)
but this gives…
ChrisF
- 260
- 4
- 16
3
votes
1 answer
Risk of changing clocktime on MySQL server host
I have a MySQL server host that have a clock that is about 75 minutes wrong. Is there any risk in changing the hosts clock while MySQL is running?
Update: Obviously NOW() and other functions returning current time will return a different result. In…
Ztyx
- 197
- 7