SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


lookup table for minutes of a day


lookup table for minutes of a day

Author
Message
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1114
Does anyone know if a website or have a query that generates a table with a row for each minute of the day starting 2 years ago and going 10 years into future?

Need it to have values in one minute increments (1440 rows/minutes for each day):
2013-10-07 04:32
2013-10-07 04:33
2013-10-07 04:34
2013-10-07 04:35
2013-10-07 04:36
2013-10-07 04:37

Thanks!
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17404 Visits: 19122
Using a Tally Table (or in this case a CTE) it's really easy to do it.
Here's an example:

WITH e1(n) AS(
SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)
),
e2(n) AS(
SELECT e1.n FROM e1, e1 x
),
e4(n) AS(
SELECT e2.n FROM e2, e2 x
),
e8(n) AS(
SELECT e4.n FROM e4, e4 x
),
cteTally(n) AS(
SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM e8
),
Test(n) AS(
SELECT DATEADD( minute, n, DATEADD( YEAR, -2, GETDATE()))
FROM cteTally)
SELECT MIN(n), MAX(n)
FROM Test
WHERE n <= DATEADD( YEAR, 10, GETDATE())



It won't give you the exact table you need but at least you'll have to analyze it to use it. If you have questions, feel free to ask.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1114
well, this gives me a table with one row containing two values:
2011-10-07 16:11:56.343 2023-10-04 16:14:56.343


I changed to Select *.

Thanks for the answer with the bonus mini - puzzle Luis. I thought I wouldn't be able to do it but I did! :-)


WITH e1(min) AS(
SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)
),
e2(min) AS(
SELECT e1.min FROM e1, e1 x
),
e4(min) AS(
SELECT e2.min FROM e2, e2 x
),
e8(min) AS(
SELECT e4.min FROM e4, e4 x
),
cteTally(min) AS(
SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
FROM e8
),
Test(min) AS(
SELECT DATEADD( minute, min, DATEADD( YEAR, -2, GETDATE()))
FROM cteTally)
SELECT *
FROM Test
WHERE min <= DATEADD( YEAR, 10, GETDATE())


polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1114
for bonus bonus, can you show me how to get just the date and minutes? eg. 2011-10-20 21:45
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17404 Visits: 19122
If you wan't for display, you can convert to string using convert.
To remove seconds from the datetime, you can do something different.
Either you specify the exact time to start or you can calculate it.

SELECT DATEADD( MINUTE, DATEDIFF( MINUTE, 0, DATEADD( YEAR, -2, GETDATE())), 0)



By the way, you can remove a CTE, I just added to show the ranges without showing all the lines.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1114
I am making a permanent table. But I need the datetime without the zeros on end.
just
2009-10-20 21:45

not
2009-10-20 21:45:00.000
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7703 Visits: 6431
polkadot (10/7/2013)
I am making a permanent table. But I need the datetime without the zeros on end.
just
2009-10-20 21:45

not
2009-10-20 21:45:00.000


A DATETIME data type will always have those zeroes on the end.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
parulprabu
parulprabu
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1490 Visits: 136
Hi,

You can try with help of replace and convert functions


REPLACE(CONVERT(CHAR(16),DATEADD( MINUTE, DATEDIFF( MINUTE, 0, DATEADD( YEAR, -2, GETDATE())), 0), 126), 'T', ' ')


polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1138 Visits: 1114
parulprabu, Thanks for the help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search