> 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 cat or the observer
>
>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) (
to Heaven 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] Date time
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 cases where the timezone needs to be preserv
> 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
because it can be important
hing 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
>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
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', '+33 hours');
>
>
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 next day" given a numeric
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
> or year (but for I18N
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,
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
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 a
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" = dd/mm/yy
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 Halliday wrote:
> >
>
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.
___
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 inser
> 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 them
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 express
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
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
_
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
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/2
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;
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.
Obviousl
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_tim
26 matches
Mail list logo