weekday time modifier

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

weekday time modifier

Kevin Martin
Hi,

Not sure if this is me misreading it, but the description of the weekday modifier in the documentation seems a bit ambiguous.

It says:

> The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.

It is not clear what happens when the date before the modifier is already the correct weekday. I interpreted this as it would advance by a full week, but it does't, it leaves the date untouched:

SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select strftime('%Y-%m-%d', '2018-09-23', 'weekday 0');
2018-09-23
sqlite>

I seem the same behaviour in 3.24.

Thanks,
Kev
_______________________________________________
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: weekday time modifier

D Burgess
select  strftime('%Y-%m-%d %H:%M', '2018-09-23 8:59', 'localtime','weekday
0');

Also gets a bit confusing. The "weekday" operates on the UTC time.


On Sun, Sep 23, 2018 at 11:42 PM, Kevin Martin <[hidden email]> wrote:

> Hi,
>
> Not sure if this is me misreading it, but the description of the weekday
> modifier in the documentation seems a bit ambiguous.
>
> It says:
>
> > The "weekday" modifier advances the date forward to the next date where
> the weekday number is N. Sunday is 0, Monday is 1, and so forth.
>
> It is not clear what happens when the date before the modifier is already
> the correct weekday. I interpreted this as it would advance by a full week,
> but it does't, it leaves the date untouched:
>
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> select strftime('%Y-%m-%d', '2018-09-23', 'weekday 0');
> 2018-09-23
> sqlite>
>
> I seem the same behaviour in 3.24.
>
> Thanks,
> Kev
> _______________________________________________
> 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: weekday time modifier

Keith Medcalf

Actually no, the calculation is based on the Julian Day number in the struct DateTime.  

When you "load" a struct DateTime from a "string", the string is parsed and the constituent parts are used to create the JulianDay (iJD) field of the structure.  This means that the iJD value is then relative to the "Local Meridian" in which the input string was expressed, not necessarily the Prime Meridian.  (A "string" which contains an offset from the Prime Meridian is converted to the appropriate Prime Meridian relative Julian Day, since such a timestamp is always a UT1 timestamp after the offset is applied).

If the load is from 'now', then the iJD is copied from the current statement iJD which originates variously from the "go get the current UT1 clock" functions which vary depending on the OS, so the iJD in the DateTime structure always contains the UT1 Julian Day.

When you use the 'localtime' modifier, the iJD is converted into the constituent parts (YMDHMS) and the OS localtime functions are used to try and determine the offset which should be applied to convert from UT1 to LMT assuming that the iJD is a UT1 Julian Day (ie, based as the Prime Meridian).  The offest so determined is then applied to the iJD stored in the DateTime structure making it no longer a UT1 Julian Day, but rather an LMT Julian Day.

The 'UTC' modifier works the same way except that is reverses the assumptions (that is, it is assumed that the iJD is LMT based, and converts it to a UT1 based Julian Day.

In any case, the calculations used for the 'start of <blah>' 'end of <blah>' weekday computations etc use whatever is stored in the iJD field of the structure.  If that is a UT1 Julian Day number, then the result is in UT1.  If the number stored in the iJD field is an LMT Julian Day, then the result is relative to that Local Meridian (not the Prime Medidian).

Technically the "Julian Day" is always relative to the Prime Meridian and thus UT1 so what is used and referred to in SQLite3 as the "Julian Day Number" is not really the "Julian Day".  It is merely an offset from the Julian Epoch assuming that the Julian Epoch does not specify the meridian to which is applied -- in other words it is a Lilian Date -- just using the julian epoch as a base -- expressed in LMT.

https://en.wikipedia.org/wiki/Lilian_date

Since the cycles hold independantly of the "local meridian" against which they are computed then the results obtained by these methods are correct, you simply must convert to an LMT-based Julian Date before performing the various calculations.  Of course the result is in LMT time with the offset at the instant of the first conversion and the end computation, although correct, may not reflect "wall clock time" since there may be changes in the "wall clock offsets" between when you first converted to LMT and when you are finished your calculations.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of D Burgess
>Sent: Sunday, 23 September, 2018 15:07
>To: SQLite mailing list
>Subject: Re: [sqlite] weekday time modifier
>
>select  strftime('%Y-%m-%d %H:%M', '2018-09-23 8:59',
>'localtime','weekday
>0');
>
>Also gets a bit confusing. The "weekday" operates on the UTC time.
>
>
>On Sun, Sep 23, 2018 at 11:42 PM, Kevin Martin <[hidden email]>
>wrote:
>
>> Hi,
>>
>> Not sure if this is me misreading it, but the description of the
>weekday
>> modifier in the documentation seems a bit ambiguous.
>>
>> It says:
>>
>> > The "weekday" modifier advances the date forward to the next date
>where
>> the weekday number is N. Sunday is 0, Monday is 1, and so forth.
>>
>> It is not clear what happens when the date before the modifier is
>already
>> the correct weekday. I interpreted this as it would advance by a
>full week,
>> but it does't, it leaves the date untouched:
>>
>> SQLite version 3.8.10.2 2015-05-20 18:17:19
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> select strftime('%Y-%m-%d', '2018-09-23', 'weekday 0');
>> 2018-09-23
>> sqlite>
>>
>> I seem the same behaviour in 3.24.
>>
>> Thanks,
>> Kev
>> _______________________________________________
>> 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