Styjun

Term for checking piece whose opponent daren't capture it

chmod would set file permission to 000 no matter what permission i try to set

Can an old DSLR be upgraded to match modern smartphone image quality

Can a wire having a 610-670 THz (frequency of blue light) AC frequency supply, generate blue light?

Replacing NiMH with Li ion

How to capture more stars?

What does it mean when you think without speaking?

Could I be denied entry into Ireland due to medical and police situations during a previous UK visit?

How do I subvert the tropes of a train heist?

Biblical Basis for 400 years of silence between old and new testament

Draw a checker pattern with a black X in the center

Why teaching kids Torah is the only forbidden profession for singles Yihud-wise?

What is/are this/these giant NASA box(es)?

What is the 中 in ダウンロード中？

Did airlines fly their aircraft slower in response to oil prices in the 1970s?

Uncommanded roll at high speed

Comment dit-on « I’ll tell you what » ?

Why is A union B also called "A or B"?

What does "tea juice" mean in this context?

I think I may have violated academic integrity last year - what should I do?

The deliberate use of misleading terminology

Beginner's snake game using PyGame

Where can I find the list of all tendons in the human body?

When a current flow in an inductor is interrupted, what limits the voltage rise?

SQLite - Get all hours from date range

Improve INSERT-per-second performance of SQLite?SQLite: express the difference as days, hours, minutes between two given datesSQL Select between datesHow to generate all dates between two datesGetting data according to last hours from sqliteSQLite vs. Oracle - Calculating date differences - hourssqlite query is not working in ios 8.1 belowSQLite query to compare datesSQLite: Sum of differences between two dates group by every dateSQLite select all entries 12 hours or less from now

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I have a SQLite query that lists all days between two dates. I want to achieve the same with the hours from those days.

This is my current query to get all days between two dates

WITH RECURSIVE dates(starttime) AS
(VALUES(min(starttime))
UNION ALL SELECT date(starttime, '+1 day')
FROM v_sessions
WHERE endtime <= max(startTime))
SELECT Date(starttime) AS Date
FROM v_sessions
GROUP BY Date
ORDER BY date

Output

---

Date
2018-10-21
2018-10-22
2018-10-23
2018-10-24
2018-10-25
2018-10-26
2018-10-27
2018-10-28
2018-10-29
2018-10-30
2018-10-31

This is the desired result of a modified query.

__________
Date
2018-10-19 01:00:00
2018-10-19 02:00:00
2018-10-19 03:00:00
2018-10-19 04:00:00
2018-10-19 05:00:00
2018-10-19 06:00:00
…..
2018-10-19 23:00:00
2018-10-20 00:00:00
2018-10-20 01:00:00
2018-10-20 02:00:00
…

sqlite

share|improve this question

edited Mar 24 at 11:11

Lara

asked Mar 24 at 9:49

LaraLara

63

add a comment | 

0

I have a SQLite query that lists all days between two dates. I want to achieve the same with the hours from those days.

This is my current query to get all days between two dates

WITH RECURSIVE dates(starttime) AS
(VALUES(min(starttime))
UNION ALL SELECT date(starttime, '+1 day')
FROM v_sessions
WHERE endtime <= max(startTime))
SELECT Date(starttime) AS Date
FROM v_sessions
GROUP BY Date
ORDER BY date

Output

---

Date
2018-10-21
2018-10-22
2018-10-23
2018-10-24
2018-10-25
2018-10-26
2018-10-27
2018-10-28
2018-10-29
2018-10-30
2018-10-31

This is the desired result of a modified query.

__________
Date
2018-10-19 01:00:00
2018-10-19 02:00:00
2018-10-19 03:00:00
2018-10-19 04:00:00
2018-10-19 05:00:00
2018-10-19 06:00:00
…..
2018-10-19 23:00:00
2018-10-20 00:00:00
2018-10-20 01:00:00
2018-10-20 02:00:00
…

sqlite

share|improve this question

edited Mar 24 at 11:11

Lara

asked Mar 24 at 9:49

LaraLara

63

add a comment | 

I have a SQLite query that lists all days between two dates. I want to achieve the same with the hours from those days.

This is my current query to get all days between two dates

WITH RECURSIVE dates(starttime) AS
(VALUES(min(starttime))
UNION ALL SELECT date(starttime, '+1 day')
FROM v_sessions
WHERE endtime <= max(startTime))
SELECT Date(starttime) AS Date
FROM v_sessions
GROUP BY Date
ORDER BY date

Output

---

Date
2018-10-21
2018-10-22
2018-10-23
2018-10-24
2018-10-25
2018-10-26
2018-10-27
2018-10-28
2018-10-29
2018-10-30
2018-10-31

This is the desired result of a modified query.

__________
Date
2018-10-19 01:00:00
2018-10-19 02:00:00
2018-10-19 03:00:00
2018-10-19 04:00:00
2018-10-19 05:00:00
2018-10-19 06:00:00
…..
2018-10-19 23:00:00
2018-10-20 00:00:00
2018-10-20 01:00:00
2018-10-20 02:00:00
…

sqlite

share|improve this question

edited Mar 24 at 11:11

Lara

asked Mar 24 at 9:49

LaraLara

63

WITH RECURSIVE dates(starttime) AS
(VALUES(min(starttime))
UNION ALL SELECT date(starttime, '+1 day')
FROM v_sessions
WHERE endtime <= max(startTime))
SELECT Date(starttime) AS Date
FROM v_sessions
GROUP BY Date
ORDER BY date

Date
2018-10-21
2018-10-22
2018-10-23
2018-10-24
2018-10-25
2018-10-26
2018-10-27
2018-10-28
2018-10-29
2018-10-30
2018-10-31

__________
Date
2018-10-19 01:00:00
2018-10-19 02:00:00
2018-10-19 03:00:00
2018-10-19 04:00:00
2018-10-19 05:00:00
2018-10-19 06:00:00
…..
2018-10-19 23:00:00
2018-10-20 00:00:00
2018-10-20 01:00:00
2018-10-20 02:00:00
…

share|improve this question

edited Mar 24 at 11:11

Lara

asked Mar 24 at 9:49

LaraLara

63

share|improve this question

edited Mar 24 at 11:11

Lara

asked Mar 24 at 9:49

LaraLara

63

asked Mar 24 at 9:49

LaraLara

63

asked Mar 24 at 9:49

LaraLara

63

2 Answers
2

active

oldest

votes

0

Basically, you need to use datetime instead of date.

The crux will be to change this date(starttime, '+1 day') to this datetime(starttime, '+1 hour'). You could use start: datetime(starttime,'start of day') and end: datetime(endtime, 'start of day','+23 hours') if you want all the hours in the specific dates, or some other date arithmetic if starttime and endtime include specific limiting hours.

share|improve this answer

answered Mar 24 at 17:27

DinoCoderSaurusDinoCoderSaurus

1,877259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Dino! I decided to solved this via C# that I am using for my use case.
  
  – Lara
  Mar 28 at 8:58
  

  
  
  
  

add a comment | 

0

I decided to solve this via C# that I am using for my use case.

 using (SQLiteConnection con = new SQLiteConnection("Data Source = data\Reporting.db; Version = 3;"))
 
 con.Open();
 using (SQLiteCommand cmd = con.CreateCommand())
 
 cmd.CommandText = queryString;
 while (startDateTime <= endDateTime)
 
 cmd.Parameters.AddWithValue("$date", startDateTime);

 maxConcurrentSessions.Rows.Add(startDateTime, Convert.ToInt32(cmd.ExecuteScalar()));

 startDateTime = startDateTime.AddHours(1);
 
 
 con.Close();
 

share|improve this answer

answered Mar 28 at 9:00

LaraLara

63

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55322511%2fsqlite-get-all-hours-from-date-range%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Basically, you need to use datetime instead of date.

The crux will be to change this date(starttime, '+1 day') to this datetime(starttime, '+1 hour'). You could use start: datetime(starttime,'start of day') and end: datetime(endtime, 'start of day','+23 hours') if you want all the hours in the specific dates, or some other date arithmetic if starttime and endtime include specific limiting hours.

share|improve this answer

answered Mar 24 at 17:27

DinoCoderSaurusDinoCoderSaurus

1,877259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Dino! I decided to solved this via C# that I am using for my use case.
  
  – Lara
  Mar 28 at 8:58
  

  
  
  
  

add a comment | 

0

Basically, you need to use datetime instead of date.

The crux will be to change this date(starttime, '+1 day') to this datetime(starttime, '+1 hour'). You could use start: datetime(starttime,'start of day') and end: datetime(endtime, 'start of day','+23 hours') if you want all the hours in the specific dates, or some other date arithmetic if starttime and endtime include specific limiting hours.

share|improve this answer

answered Mar 24 at 17:27

DinoCoderSaurusDinoCoderSaurus

1,877259

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Dino! I decided to solved this via C# that I am using for my use case.
  
  – Lara
  Mar 28 at 8:58
  

  
  
  
  

add a comment | 

Basically, you need to use datetime instead of date.

The crux will be to change this date(starttime, '+1 day') to this datetime(starttime, '+1 hour'). You could use start: datetime(starttime,'start of day') and end: datetime(endtime, 'start of day','+23 hours') if you want all the hours in the specific dates, or some other date arithmetic if starttime and endtime include specific limiting hours.

share|improve this answer

answered Mar 24 at 17:27

DinoCoderSaurusDinoCoderSaurus

1,877259

share|improve this answer

answered Mar 24 at 17:27

DinoCoderSaurusDinoCoderSaurus

1,877259

answered Mar 24 at 17:27

DinoCoderSaurusDinoCoderSaurus

1,877259

answered Mar 24 at 17:27

DinoCoderSaurusDinoCoderSaurus

1,877259

0

I decided to solve this via C# that I am using for my use case.

 using (SQLiteConnection con = new SQLiteConnection("Data Source = data\Reporting.db; Version = 3;"))
 
 con.Open();
 using (SQLiteCommand cmd = con.CreateCommand())
 
 cmd.CommandText = queryString;
 while (startDateTime <= endDateTime)
 
 cmd.Parameters.AddWithValue("$date", startDateTime);

 maxConcurrentSessions.Rows.Add(startDateTime, Convert.ToInt32(cmd.ExecuteScalar()));

 startDateTime = startDateTime.AddHours(1);
 
 
 con.Close();
 

share|improve this answer

answered Mar 28 at 9:00

LaraLara

63

add a comment | 

0

I decided to solve this via C# that I am using for my use case.

 using (SQLiteConnection con = new SQLiteConnection("Data Source = data\Reporting.db; Version = 3;"))
 
 con.Open();
 using (SQLiteCommand cmd = con.CreateCommand())
 
 cmd.CommandText = queryString;
 while (startDateTime <= endDateTime)
 
 cmd.Parameters.AddWithValue("$date", startDateTime);

 maxConcurrentSessions.Rows.Add(startDateTime, Convert.ToInt32(cmd.ExecuteScalar()));

 startDateTime = startDateTime.AddHours(1);
 
 
 con.Close();
 

share|improve this answer

answered Mar 28 at 9:00

LaraLara

63

add a comment | 

I decided to solve this via C# that I am using for my use case.

 using (SQLiteConnection con = new SQLiteConnection("Data Source = data\Reporting.db; Version = 3;"))
 
 con.Open();
 using (SQLiteCommand cmd = con.CreateCommand())
 
 cmd.CommandText = queryString;
 while (startDateTime <= endDateTime)
 
 cmd.Parameters.AddWithValue("$date", startDateTime);

 maxConcurrentSessions.Rows.Add(startDateTime, Convert.ToInt32(cmd.ExecuteScalar()));

 startDateTime = startDateTime.AddHours(1);
 
 
 con.Close();
 

share|improve this answer

answered Mar 28 at 9:00

LaraLara

63

 using (SQLiteConnection con = new SQLiteConnection("Data Source = data\Reporting.db; Version = 3;"))
 
 con.Open();
 using (SQLiteCommand cmd = con.CreateCommand())
 
 cmd.CommandText = queryString;
 while (startDateTime <= endDateTime)
 
 cmd.Parameters.AddWithValue("$date", startDateTime);

 maxConcurrentSessions.Rows.Add(startDateTime, Convert.ToInt32(cmd.ExecuteScalar()));

 startDateTime = startDateTime.AddHours(1);
 
 
 con.Close();
 

share|improve this answer

answered Mar 28 at 9:00

LaraLara

63

answered Mar 28 at 9:00

LaraLara

63

answered Mar 28 at 9:00

LaraLara

63