Date time functions not working

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
28 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Date time functions not working

Matthew Halliday
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 strftime('%Y-%m-%d',date_time , '-1 days') as DATE from
tmp_dspace_import

...and numerous other variations.  But NO!

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.  I have tried changing the
date_time collumn format to INTEGER and REAL, alond with all variations to
the query known to man (well to me anyway) and no change.

Checked my syntax, all commas in the right places, quotes look good.  head
getting sore from banging it on the desk.

I'm on SQLite v3.20 fwiw.  I'm testing it in the SQLiteStudio query browser
first before running in a Powershell reporting script, but this part simply
does not work and its the only part that doesn't.

The date data is in the format: "21/08/2017 16:27:03".  I thought TEXT
stored it in the ISO "YYYY/MM/DD" format?

The plan is to produce a daily report, as well as a weekly and monthly
report.  To do that I need to get these dates working.

Can anyone please help me?  It must be something really simple I'm
missing.  Am I going to have to split it into the component parts and do it
that way?

Matt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Clemens Ladisch
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.

Obviously, the problem is with the longer query.  Which you have not shown.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Matthew Halliday
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;


Result:

server_01    C:    24/08/2017 04:02:31    -312
server_01     D:    24/08/2017 04:02:31    0
server_01     E:    24/08/2017 04:02:31    0


SELECT servername,

date_time,

drive,

SUM(diff_used) AS DailyUsed

FROM tmp_dspace_import

WHERE date_time = strftime(date_time, -2)

GROUP BY servername,

drive;


gives data from last 3 days.


21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
21/08/2017 14:33:09
23/08/2017 11:45:33
23/08/2017 11:45:33
23/08/2017 11:45:33
24/08/2017 04:02:20
24/08/2017 04:02:20
24/08/2017 04:02:20

On Thu, Aug 24, 2017 at 10:27 AM, Clemens Ladisch <[hidden email]>
wrote:

> 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.
>
> Obviously, the problem is with the longer query.  Which you have not shown.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Radovan Antloga
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
> 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;
>
>
> Result:
>
> server_01    C:    24/08/2017 04:02:31    -312
> server_01     D:    24/08/2017 04:02:31    0
> server_01     E:    24/08/2017 04:02:31    0
>
>
> SELECT servername,
>
> date_time,
>
> drive,
>
> SUM(diff_used) AS DailyUsed
>
> FROM tmp_dspace_import
>
> WHERE date_time = strftime(date_time, -2)
>
> GROUP BY servername,
>
> drive;
>
>
> gives data from last 3 days.
>
>
> 21/08/2017 14:33:09
> 21/08/2017 14:33:09
> 21/08/2017 14:33:09
> 21/08/2017 14:33:09
> 21/08/2017 14:33:09
> 21/08/2017 14:33:09
> 23/08/2017 11:45:33
> 23/08/2017 11:45:33
> 23/08/2017 11:45:33
> 24/08/2017 04:02:20
> 24/08/2017 04:02:20
> 24/08/2017 04:02:20
>
> On Thu, Aug 24, 2017 at 10:27 AM, Clemens Ladisch <[hidden email]>
> wrote:
>
>> 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.
>> Obviously, the problem is with the longer query.  Which you have not shown.
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Matthew Halliday
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 day')


GROUP BY date_time,

servername,

drive;


returns a blank set.  Does the order in the query make a difference?


Its probably me missing something here.

On Thu, Aug 24, 2017 at 10:48 AM, Radovan Antloga <[hidden email]>
wrote:

> 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
>> 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;
>>
>>
>> Result:
>>
>> server_01    C:    24/08/2017 04:02:31    -312
>> server_01     D:    24/08/2017 04:02:31    0
>> server_01     E:    24/08/2017 04:02:31    0
>>
>>
>> SELECT servername,
>>
>> date_time,
>>
>> drive,
>>
>> SUM(diff_used) AS DailyUsed
>>
>> FROM tmp_dspace_import
>>
>> WHERE date_time = strftime(date_time, -2)
>>
>> GROUP BY servername,
>>
>> drive;
>>
>>
>> gives data from last 3 days.
>>
>>
>> 21/08/2017 14:33:09
>> 21/08/2017 14:33:09
>> 21/08/2017 14:33:09
>> 21/08/2017 14:33:09
>> 21/08/2017 14:33:09
>> 21/08/2017 14:33:09
>> 23/08/2017 11:45:33
>> 23/08/2017 11:45:33
>> 23/08/2017 11:45:33
>> 24/08/2017 04:02:20
>> 24/08/2017 04:02:20
>> 24/08/2017 04:02:20
>>
>> On Thu, Aug 24, 2017 at 10:27 AM, Clemens Ladisch <[hidden email]>
>> wrote:
>>
>> 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.
>>>>
>>> Obviously, the problem is with the longer query.  Which you have not
>>> shown.
>>>
>>>
>>> Regards,
>>> Clemens
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Clemens Ladisch
In reply to this post by Matthew Halliday
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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Matthew Halliday
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 total
report.   These will be generated from a Powershell script - that bit works
fine.

Matt

On Thu, Aug 24, 2017 at 11:07 AM, Clemens Ladisch <[hidden email]>
wrote:

> 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
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Radovan Antloga
In reply to this post by Matthew Halliday
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 expression: date_time >= julianday(current_date) - 1

Just yesterday is then: date_time = julianday(current_date) - 1

for two days back: date_time >= julianday(current_date) - 2

etc...

Regards
Radovan


Matthew Halliday je 24.08.2017 ob 12:06 napisal:

> 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 day')
>
>
> GROUP BY date_time,
>
> servername,
>
> drive;
>
>
> returns a blank set.  Does the order in the query make a difference?
>
>
> Its probably me missing something here.
>
> On Thu, Aug 24, 2017 at 10:48 AM, Radovan Antloga <[hidden email]>
> wrote:
>
>> 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
>>> 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;
>>>
>>>
>>> Result:
>>>
>>> server_01    C:    24/08/2017 04:02:31    -312
>>> server_01     D:    24/08/2017 04:02:31    0
>>> server_01     E:    24/08/2017 04:02:31    0
>>>
>>>
>>> SELECT servername,
>>>
>>> date_time,
>>>
>>> drive,
>>>
>>> SUM(diff_used) AS DailyUsed
>>>
>>> FROM tmp_dspace_import
>>>
>>> WHERE date_time = strftime(date_time, -2)
>>>
>>> GROUP BY servername,
>>>
>>> drive;
>>>
>>>
>>> gives data from last 3 days.
>>>
>>>
>>> 21/08/2017 14:33:09
>>> 21/08/2017 14:33:09
>>> 21/08/2017 14:33:09
>>> 21/08/2017 14:33:09
>>> 21/08/2017 14:33:09
>>> 21/08/2017 14:33:09
>>> 23/08/2017 11:45:33
>>> 23/08/2017 11:45:33
>>> 23/08/2017 11:45:33
>>> 24/08/2017 04:02:20
>>> 24/08/2017 04:02:20
>>> 24/08/2017 04:02:20
>>>
>>> On Thu, Aug 24, 2017 at 10:27 AM, Clemens Ladisch <[hidden email]>
>>> wrote:
>>>
>>> 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.
>>>>>
>>>> Obviously, the problem is with the longer query.  Which you have not
>>>> shown.
>>>>
>>>>
>>>> Regards,
>>>> Clemens
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

David Raymond
In reply to this post by Matthew Halliday

> The date data is in the format: "21/08/2017 16:27:03".  I thought TEXT
> stored it in the ISO "YYYY/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 YYYY-MM-DD HH:MM:SS so if you're giving them slashes and a different order then they're all gonna mess up.

> 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

That's saying use date_time as the format string where -2 is the time string. I'm surprised that actually gave an answer rather than an error. And that you're getting a result with slashes is really really suspicious.


> 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 expression: date_time >= julianday(current_date) - 1
>
> Just yesterday is then: date_time = julianday(current_date) - 1
>
> for two days back: date_time >= julianday(current_date) - 2
>
> etc...

Don't forget that if you have a date and time in the database, then you're comparing a whole time to just a day there. I think you'd want something like

where date(date_time) = date(current_date, "-1 days")  --for yesterday
where date(date_time) >= date(current_date, "-6 days") --for within the last week
where date(date_time) between date(current_date, "-3 days") and date(current_date, "-1 days") --3 days ago to yesterday.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Matthew Halliday
Looks like it's me.

VBScript "NOW" = dd/mm/yyyy whereas ISO "NOW" = yyyy/mm/dd.

Doh!

Last part of the INSERT string reads:

      ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');"

Changing it to:

      ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') ); so
it inserts the SQLite 'NOW' instead.

See how well that works.  I need to update the other date/time stamps
though.



On Thu, Aug 24, 2017 at 2:06 PM, David Raymond <[hidden email]>
wrote:

>
> > The date data is in the format: "21/08/2017 16:27:03".  I thought TEXT
> > stored it in the ISO "YYYY/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 YYYY-MM-DD
> HH:MM:SS so if you're giving them slashes and a different order then
> they're all gonna mess up.
>
> > 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
>
> That's saying use date_time as the format string where -2 is the time
> string. I'm surprised that actually gave an answer rather than an error.
> And that you're getting a result with slashes is really really suspicious.
>
>
> > 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 expression: date_time >= julianday(current_date) - 1
> >
> > Just yesterday is then: date_time = julianday(current_date) - 1
> >
> > for two days back: date_time >= julianday(current_date) - 2
> >
> > etc...
>
> Don't forget that if you have a date and time in the database, then you're
> comparing a whole time to just a day there. I think you'd want something
> like
>
> where date(date_time) = date(current_date, "-1 days")  --for yesterday
> where date(date_time) >= date(current_date, "-6 days") --for within the
> last week
> where date(date_time) between date(current_date, "-3 days") and
> date(current_date, "-1 days") --3 days ago to yesterday.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Warren Young
On Aug 24, 2017, at 7:21 AM, Matthew Halliday <[hidden email]> wrote:
>
> VBScript "NOW" = dd/mm/yyyy

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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Matthew Halliday
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 <[hidden email]> wrote:

> On Aug 24, 2017, at 7:21 AM, Matthew Halliday <[hidden email]> wrote:
> >
> > VBScript "NOW" = dd/mm/yyyy
>
> 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.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Radovan Antloga
In reply to this post by Matthew Halliday
You must have ISO8601 that has format
YYYY-MM-DD not YYYY/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" = dd/mm/yyyy whereas ISO "NOW" = yyyy/mm/dd.
>
> Doh!
>
> Last part of the INSERT string reads:
>
>        ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');"
>
> Changing it to:
>
>        ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') ); so
> it inserts the SQLite 'NOW' instead.
>
> See how well that works.  I need to update the other date/time stamps
> though.
>
>
>
> On Thu, Aug 24, 2017 at 2:06 PM, David Raymond <[hidden email]>
> wrote:
>
>>> The date data is in the format: "21/08/2017 16:27:03".  I thought TEXT
>>> stored it in the ISO "YYYY/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 YYYY-MM-DD
>> HH:MM:SS so if you're giving them slashes and a different order then
>> they're all gonna mess up.
>>
>>> 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
>> That's saying use date_time as the format string where -2 is the time
>> string. I'm surprised that actually gave an answer rather than an error.
>> And that you're getting a result with slashes is really really suspicious.
>>
>>
>>> 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 expression: date_time >= julianday(current_date) - 1
>>>
>>> Just yesterday is then: date_time = julianday(current_date) - 1
>>>
>>> for two days back: date_time >= julianday(current_date) - 2
>>>
>>> etc...
>> Don't forget that if you have a date and time in the database, then you're
>> comparing a whole time to just a day there. I think you'd want something
>> like
>>
>> where date(date_time) = date(current_date, "-1 days")  --for yesterday
>> where date(date_time) >= date(current_date, "-6 days") --for within the
>> last week
>> where date(date_time) between date(current_date, "-3 days") and
>> date(current_date, "-1 days") --3 days ago to yesterday.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Matthew Halliday
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 and look at it fresh
tomorrow morning.

On Thu, Aug 24, 2017 at 3:07 PM, Radovan Antloga <[hidden email]>
wrote:

> You must have ISO8601 that has format
> YYYY-MM-DD not YYYY/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" = dd/mm/yyyy whereas ISO "NOW" = yyyy/mm/dd.
>>
>> Doh!
>>
>> Last part of the INSERT string reads:
>>
>>        ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');"
>>
>> Changing it to:
>>
>>        ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') );
>> so
>> it inserts the SQLite 'NOW' instead.
>>
>> See how well that works.  I need to update the other date/time stamps
>> though.
>>
>>
>>
>> On Thu, Aug 24, 2017 at 2:06 PM, David Raymond <[hidden email]>
>> wrote:
>>
>> The date data is in the format: "21/08/2017 16:27:03".  I thought TEXT
>>>> stored it in the ISO "YYYY/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 YYYY-MM-DD
>>> HH:MM:SS so if you're giving them slashes and a different order then
>>> they're all gonna mess up.
>>>
>>> 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
>>>>
>>> That's saying use date_time as the format string where -2 is the time
>>> string. I'm surprised that actually gave an answer rather than an error.
>>> And that you're getting a result with slashes is really really
>>> suspicious.
>>>
>>>
>>> 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 expression: date_time >= julianday(current_date) - 1
>>>>
>>>> Just yesterday is then: date_time = julianday(current_date) - 1
>>>>
>>>> for two days back: date_time >= julianday(current_date) - 2
>>>>
>>>> etc...
>>>>
>>> Don't forget that if you have a date and time in the database, then
>>> you're
>>> comparing a whole time to just a day there. I think you'd want something
>>> like
>>>
>>> where date(date_time) = date(current_date, "-1 days")  --for yesterday
>>> where date(date_time) >= date(current_date, "-6 days") --for within the
>>> last week
>>> where date(date_time) between date(current_date, "-3 days") and
>>> date(current_date, "-1 days") --3 days ago to yesterday.
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

R Smith
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 expect to see?

That should allow us to point you fast and efficiently towards the best
solution.
Cheers!


On 2017/08/24 5:14 PM, Matthew Halliday wrote:

> 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 and look at it fresh
> tomorrow morning.
>
> On Thu, Aug 24, 2017 at 3:07 PM, Radovan Antloga <[hidden email]>
> wrote:
>
>> You must have ISO8601 that has format
>> YYYY-MM-DD not YYYY/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" = dd/mm/yyyy whereas ISO "NOW" = yyyy/mm/dd.
>>>
>>> Doh!
>>>
>>> Last part of the INSERT string reads:
>>>
>>>         ,'" & strFreeSpace & "','" & pctFreeSpace & "','" & NOW & "');"
>>>
>>> Changing it to:
>>>
>>>         ,'" & strFreeSpace & "','" & pctFreeSpace & "', datetime('NOW') );
>>> so
>>> it inserts the SQLite 'NOW' instead.
>>>
>>> See how well that works.  I need to update the other date/time stamps
>>> though.
>>>
>>>
>>>
>>> On Thu, Aug 24, 2017 at 2:06 PM, David Raymond <[hidden email]>
>>> wrote:
>>>
>>> The date data is in the format: "21/08/2017 16:27:03".  I thought TEXT
>>>>> stored it in the ISO "YYYY/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 YYYY-MM-DD
>>>> HH:MM:SS so if you're giving them slashes and a different order then
>>>> they're all gonna mess up.
>>>>
>>>> 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
>>>>>
>>>> That's saying use date_time as the format string where -2 is the time
>>>> string. I'm surprised that actually gave an answer rather than an error.
>>>> And that you're getting a result with slashes is really really
>>>> suspicious.
>>>>
>>>>
>>>> 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 expression: date_time >= julianday(current_date) - 1
>>>>>
>>>>> Just yesterday is then: date_time = julianday(current_date) - 1
>>>>>
>>>>> for two days back: date_time >= julianday(current_date) - 2
>>>>>
>>>>> etc...
>>>>>
>>>> Don't forget that if you have a date and time in the database, then
>>>> you're
>>>> comparing a whole time to just a day there. I think you'd want something
>>>> like
>>>>
>>>> where date(date_time) = date(current_date, "-1 days")  --for yesterday
>>>> where date(date_time) >= date(current_date, "-6 days") --for within the
>>>> last week
>>>> where date(date_time) between date(current_date, "-3 days") and
>>>> date(current_date, "-1 days") --3 days ago to yesterday.
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Jens Alfke-2
In reply to this post by Matthew Halliday
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, there are a lot of frustrations involved in working with these strings.

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 or year (but for I18N purposes you don't want to hardcode Western calendars into your code anyway.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

amalgamation file of Version 3.19.4?

麦轲数据管家
dear everyone:
  i want upgrade my SQLite to version 3.19.4, but i can find  the amalgamation file of Version 3.19.4,
  do anyone have it or where i can download a copy?

thanks
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: amalgamation file of Version 3.19.4?

Richard Hipp-3
On 8/24/17, 麦轲数据管家 <[hidden email]> wrote:
> dear everyone:
>   i want upgrade my SQLite to version 3.19.4, but i can find  the
> amalgamation file of Version 3.19.4,
>   do anyone have it or where i can download a copy?

We have not released a precompiled amalgamation for 3.19.4.  You'll
need to build it yourself from canonical sources.

(1) Download the source tarball from
https://sqlite.org/src/tarball/sqlite.tar.gz?uuid=version-3.19.4

(2) Unpack the tarball.

(3a) On unix: ./configure; make sqlite3.c

or

(3b) On windows:  msvc /f makefile.msc sqlite3.c

You will need to have a recent "tclsh" installed on your machine in
order to run step 3.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Simon Slavin-3
In reply to this post by Jens Alfke-2


On 25 Aug 2017, at 1:19am, Jens Alfke <[hidden email]> 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 or year (but for I18N purposes you don't want to hardcode Western calendars into your code anyway.)

Either part of (b) or item (c):

Numeric timestamps make it easy to do "24 hours after this timestamp".  But they make it difficult to do "9am the next day".

Because of this, in cases where filespace is not a problem I’ve sometimes stored both Unix epoch and a text timestamp.  Yes, it’s redundant data, but I know that whatever calculation I need to do with the timestamp will be easy, and that speeds up both my program and my programming.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Date time functions not working

Richard Hipp-3
On 8/24/17, Simon Slavin <[hidden email]> 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 next day" given a numeric
timestamp as a julian day number:

     SELECT datetime(numericJDTime, 'start of day', '+33 hours');

If you timestamp is unix (seconds since 1970):

     SELECT datetime(numericUnixTime, 'unixepoch', 'start of day', '+33 hours');

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12