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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 1257
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!

--Quote me
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42594 Visits: 19846
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 1257
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())



--Quote me
polkadot
polkadot
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 1257
for bonus bonus, can you show me how to get just the date and minutes? eg. 2011-10-20 21:45

--Quote me
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42594 Visits: 19846
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 1257
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

--Quote me
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18139 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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1608 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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3156 Visits: 1257
parulprabu, Thanks for the help.

--Quote me
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