Re: [sqlite] julianday and unixepoch

[Petite Abeille]

On Sep 17, 2011, at 10:53 PM, Kevin Benson wrote:

>> Your point is taken and most likely the documentation intended merely
> to identify the result in terms of Julian days:
> i.e.
> The julianday() function returns the number of Julian
> days since noon in Greenwich on November 24, 4714 B.C. (Proleptic
> Gregorian calendar

All in all, I think the documentation is a bit misleading. It should read 
something along these lines instead:

"The julianday() function returns the Julian date, the interval of time in days 
and fractions of a day since January 1, 4713 BC Greenwich noon, Julian 
proleptic calendar." 

Furthermore, all references to Julian Day Number should be removed and/or 
replace by Julian Date.

P.S.

As far as I know, the Julian date starts on January 1, 4713 BC Greenwich noon, 
not November 24, 4714 B.C.

The Julian date for BCE  4713 January  1 12:00:00.0 UT is
JD  0.00




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] julianday and unixepoch

[Kevin Benson]

On Sat, Sep 17, 2011 at 4:43 PM, Petite Abeille wrote:

>
> On Sep 17, 2011, at 10:42 PM, Kevin Benson wrote:
>
> > The julianday() function returns the Julian
> > day- the number of days...
> >
> >  since noon  in Greenwich on November 24, 4714
> > B.C. (Proleptic
> > Gregorian calendar<
> http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar>).
>
> The SQLite documentation explicitly refers to Julian Day Number (JDN), as
> opposed to Julian Date (JD):
>
> %J  Julian day number
>
> http://www.sqlite.org/lang_datefunc.html
>
> Not to split hairs, but there is a practical differences between a Julian
> Day Number and a Julian Date:
>
> "The Julian date (JD) is the interval of time in days and fractions of a
> day since January 1, 4713 BC Greenwich noon, Julian proleptic calendar."
>
> "The Julian day number (JDN) is the integer part of the Julian date (JD)."
>
> http://asa.usno.navy.mil/SecM/Glossary.html#j
>
>  Your point is taken and most likely the documentation intended merely
to identify the result in terms of Julian days:
i.e.
The julianday() function returns the number of Julian
days since noon in Greenwich on November 24, 4714 B.C. (Proleptic
Gregorian calendar
).
--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] julianday and unixepoch

[Petite Abeille]

On Sep 17, 2011, at 10:42 PM, Kevin Benson wrote:

> The julianday() function returns the Julian
> day- the number of days...
> 
>  since noon  in Greenwich on November 24, 4714
> B.C. (Proleptic
> Gregorian 
> calendar).

The SQLite documentation explicitly refers to Julian Day Number (JDN), as 
opposed to Julian Date (JD):

%J  Julian day number

http://www.sqlite.org/lang_datefunc.html

Not to split hairs, but there is a practical differences between a Julian Day 
Number and a Julian Date:

"The Julian date (JD) is the interval of time in days and fractions of a day 
since January 1, 4713 BC Greenwich noon, Julian proleptic calendar."

"The Julian day number (JDN) is the integer part of the Julian date (JD)."

http://asa.usno.navy.mil/SecM/Glossary.html#j

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] julianday and unixepoch

[Kevin Benson]

On Sat, Sep 17, 2011 at 3:52 PM, Petite Abeille wrote:

>
> > So how come does julianday take the time part of date into consideration
> even when it's not provided?
> >
>
> In SQLite:
>
> > select julianday( '2002-08-28' );
> 2452514.5
>
*SNIP*


> I was expecting a julian day number. SQLite's julianday function returned a
> julian date (including a time portion).
>
>

http://www.sqlite.org/lang_datefunc.html


The julianday() function returns the Julian
day- the number of days...

 since noon  in Greenwich on November 24, 4714
B.C. (Proleptic
Gregorian calendar).



--
   --
  --
 --ô¿ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] julianday and unixepoch

[Petite Abeille]

On Sep 17, 2011, at 9:29 PM, Igor Tandetnik wrote:

>> So how come does julianday take the time part of date into consideration 
>> even when it's not provided?
> 
> It doesn't.

Hmmm...

http://en.wikipedia.org/wiki/Julian_day#Converting_Gregorian_calendar_date_to_Julian_Day_Number

local math = require( 'math' )
local floor = math.floor
local year = 2002
local month = 8
local day = 28
local a = floor( ( 14 - month ) / 12 )
local y = year + 4800 - a
local m = month + 12 * a - 3
local jdn = day + floor( ( 153 * m + 2 ) / 5 ) + 365 * y + floor( y / 4 ) - 
floor( y / 100 ) + floor( y/ 400 ) - 32045

print( jdn )

> 2452515

In SQLite:

> select julianday( '2002-08-28' );
2452514.5

> 
>> JD 2452514.50 is
>> CE 2002 August 28 00:00:00.0 UT  Wednesday
>> 
>> JD 2452514.00 is
>> CE 2002 August 27 12:00:00.0 UT  Tuesday
> 
> Looks OK to me. What again seems to be the problem?

I was expecting a julian day number. SQLite's julianday function returned a 
julian date (including a time portion).

>> Or did I miss something?
> 
> Apparently.

Perhaps :)

In any case, will stick with cast and round and call it a (julian) day:

> select cast( round( julianday( date( 1030561675, 'unixepoch', 'utc' ) ) ) as 
> integer );
2452515



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] julianday and unixepoch

[Igor Tandetnik]

Petite Abeille  wrote:
> Say, I would like to convert that date to a julian day:
> 
>> select julianday( date( 1030561675, 'unixepoch', 'utc'  ) );
> 2452514.5
> 
> Oooops... where does that .5 comes from?

In Julian calendar, 0 represents noon, and .5 represents midnight.

> I was more expecting the following:
>> select cast( julianday( date( 1030561675, 'unixepoch', 'utc' ) ) as integer 
>> );
> 2452514

What was that expectation based on?

> So how come does julianday take the time part of date into consideration even 
> when it's not provided?

It doesn't.

> JD 2452514.50 is
> CE 2002 August 28 00:00:00.0 UT  Wednesday
>
> JD 2452514.00 is
> CE 2002 August 27 12:00:00.0 UT  Tuesday

Looks OK to me. What again seems to be the problem?

> Or did I miss something?

Apparently.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users