Re: [sqlite] Date time functions not working

> On Aug 25, 2017, at 10:35 AM, Keith Medcalf wrote: > > Which "localtime"? The "localtime" according to Schroedingers' Cat or the > "localtime" of the observer? Or the "localtime" at which the cat-boxer put > the cat in the box -- and is that by the reckoning of the

Re: [sqlite] Date time functions not working

>This is why ISO8601 format was invented and why it should be used. >The "instant time offset" from UT1 (which is not the timezone) should >only ever be excluded if the datetime expressed is already in UT1. The caveat here of course being that UT1 times with offsets (stored as ISO8601 strings)

Re: [sqlite] Date time functions not working

eaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke >Sent: Friday, 25 August, 2017 10:46 >To: SQLite mailing list >Subject: Re: [sqlite] Dat

Re: [sqlite] Date time functions not working

On Friday, 25 August, 2017 10:46, Jens Alfke said: >> On Aug 24, 2017, at 6:28 PM, Keith Medcalf wrote: >> Timezone is something that is applied and removed at the User >Interface level and should never exist below the presentation level. >There are

Re: [sqlite] Date time functions not working

> On Aug 24, 2017, at 6:28 PM, Keith Medcalf wrote: > > Timezone is something that is applied and removed at the User Interface level > and should never exist below the presentation level. There are cases where the timezone needs to be preserved in the data model

Re: [sqlite] Date time functions not working

g compared to the slowdown of a human trying to parse a single number instead of a timestring. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jens Alfke Sent: Thursday, August 24, 2017 8:19 PM To: SQLite mailing list Subject: Re: [sqlite]

Re: [sqlite] Date time functions not working

>It's more efficient to store timestamps as numbers (e.g. the standard >Unix time_t, or some other "number of seconds since a specific epoch" >value, as all platforms have.) >Parsing date/time strings is surprisingly expensive compared to just >reading and writing numeric values. And as you've

Re: [sqlite] Date time functions not working

On 25 Aug 2017, at 2:10am, Richard Hipp wrote: > See the "Modifiers" section of https://sqlite.org/lang_datefunc.html > > To get the date-and-time for "9am the next day" given a numeric > timestamp as a julian day number: > > SELECT datetime(numericJDTime, 'start of day',

Re: [sqlite] Date time functions not working

On 8/24/17, Simon Slavin wrote: > > Numeric timestamps make it easy to do "24 hours after this timestamp". But > they make it difficult to do "9am the next day". See the "Modifiers" section of https://sqlite.org/lang_datefunc.html To get the date-and-time for "9am the

Re: [sqlite] Date time functions not working

On 25 Aug 2017, at 1:19am, Jens Alfke wrote: > The only drawbacks of numeric timestamps are that > (a) they don't directly store a timezone (you have to add that as another > column), and > (b) they don't make it easy to do calendar operations like getting the month >

Re: [sqlite] Date time functions not working

It's more efficient to store timestamps as numbers (e.g. the standard Unix time_t, or some other "number of seconds since a specific epoch" value, as all platforms have.) Parsing date/time strings is surprisingly expensive compared to just reading and writing numeric values. And as you've seen,

Re: [sqlite] Date time functions not working

Hi Matthew, Do not wish to sound like a forum troll, but your descriptions are a bit loosely worded making it difficult to follow, but we'd really like to help, so... Could you give us a small bit of real sample data from your table(s), and the REAL query you use, and perhaps the output you

Re: [sqlite] Date time functions not working

I realised that - it was simply a typo. the delimeter was always a '-' as below. I now have all the date_time data in the correct ISO format: 2017-08-21 11:43:05 2017-08-21 11:43:05 2017-08-21 11:43:05 2017-08-21 11:43:05 2017-08-21 11:43:05 And it still won't work. Going to leave it for now

Re: [sqlite] Date time functions not working

You must have ISO8601 that has format -MM-DD not /MM/DD. If you compare strings it will not work. See result of this query: select date('now', '-1 days') you get: 2017-08-23 Regards Radovan Matthew Halliday je 24.08.2017 ob 15:21 napisal: Looks like it's me. VBScript "NOW" =

Re: [sqlite] Date time functions not working

DOH! Its set to UK but date wrong way round. Thought I checked that. Laptop was provided pre-imaged and locked down. No admin rights - had to fight hard for them! Fixed it. On Thu, Aug 24, 2017 at 2:30 PM, Warren Young wrote: > On Aug 24, 2017, at 7:21 AM, Matthew

Re: [sqlite] Date time functions not working

On Aug 24, 2017, at 7:21 AM, Matthew Halliday wrote: > > VBScript "NOW" = dd/mm/ Surely that’s dependent on your local time settings in the Control Panel? Try changing your locale to UK or similar. I bet you’ll find the date format changes, too.

Re: [sqlite] Date time functions not working

Looks like it's me. VBScript "NOW" = dd/mm/ whereas ISO "NOW" = /mm/dd. Doh! Last part of the INSERT string reads: ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');" Changing it to: ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') ); so it

Re: [sqlite] Date time functions not working

> The date data is in the format: "21/08/2017 16:27:03". I thought TEXT > stored it in the ISO "/MM/DD" format? What you declare the field as doesn't matter, so much as what you actually store in it. But all the functions apart from strftime use -MM-DD HH:MM:SS so if you're giving

Re: [sqlite] Date time functions not working

Then you can have query like this: select date_time, servername, drive, sum(diff_used) DailyUsed_mb from tmp_dspace_import where date_time >= julianday(current_date) - 1 group by date_time, servername, drive This would be for yesterday and today. If you need more days in past then change

Re: [sqlite] Date time functions not working

For now I just want the previous day. Yesterday. midnight to midnight. I eventually want to go back and do each of the last 10 days individually for historical reasons, but for automated reporting I want the previous day. After that I want to doa weekly usage agregated total and a monthly

Re: [sqlite] Date time functions not working

Matthew Halliday wrote: > select servername, drive, strftime(date_time, -2) as iDATE, SUM(diff_used) AS > DailyUsed > from tmp_dspace_import > group by servername, drive; When there are multiple table rows in a group, which date should be returned? Regards, Clemens

Re: [sqlite] Date time functions not working

Hi Radovan, just tried that - if I use strftime(date_time, -1) it still gives me today, but also the last 4 days. If I use date(date_time,'-1 days') I get a NULL. SELECT date_time, servername, drive, SUM(diff_used) AS DailyUsed_mb FROM tmp_dspace_import where date_time = date('now',' -1

Re: [sqlite] Date time functions not working

Hi, you don't have all columns in group by. You should have grup by 1,2,3. Column 3 is expression. Regards, Radovan Matthew Halliday je 24.08.2017 ob 11:41 napisal: Correct: SELECT strftime(date_time, -2) as iDATE from tmp_dspace_import Result 21/08/2017 11:43:05 21/08/2017 11:43:05

Re: [sqlite] Date time functions not working

Correct: SELECT strftime(date_time, -2) as iDATE from tmp_dspace_import Result 21/08/2017 11:43:05 21/08/2017 11:43:05 21/08/2017 11:43:05 Not working: select servername, drive, strftime(date_time, -2) as iDATE, SUM(diff_used) AS DailyUsed from tmp_dspace_import group by servername, drive;

Re: [sqlite] Date time functions not working

Matthew Halliday wrote: > SELECT strftime('%Y-%m-%d',date_time , '-1 days') as DATE from > tmp_dspace_import > > If I run it as a stand-alone single statement it works. > > If I run it as part of a longer query I get either just 131 rows of just > today's data or a collumn of NULL values.

[sqlite] Date time functions not working

Morning All I'm being driven nuts by dates. I have a 'date_time' collumn currently set to TEXT. I am simply trying to get the records from yesterday. So the following should work: SELECT date(date_time,'-1 days') as DATE from tmp_dspace_import as should... SELECT