bigint to timestamp

[John Machin]

On Jan 29, 10:00 am, John Machin <sjmac... at lexicon.net> wrote:
> On Jan 29, 1:05 am, Jon Clements <jon... at googlemail.com> wrote:
>
>
>
> > On Jan 28, 1:50 pm, Steve Holden <st... at holdenweb.com> wrote:
>
> > > Shah Sultan Alam wrote:
> > > > Hi Group,
> > > > I have file with contents retrieved from mysql DB.
> > > > which has a time field with type defined bigint(20)
> > > > I want to parse that field into timestamp format(YYYY-MM-DD HH:MM:SS
> > > > GMT) using python code.
> > > > The value I found for that field is 212099016004150509
> > > > Give me sample code that does the conversion.
>
> > > Please?
>
> > > Perhaps you could tell us what date and time 212099016004150509 is
> > > supposed to represent? The classic format is "seconds since the Unix
> > > epoch" but that isn't what this is:
>
> > > >>> time.localtime(212099016004150509)
>
> > > Traceback (most recent call last):
> > >   File "<stdin>", line 1, in <module>
> > > ValueError: timestamp out of range for platform time_t
>
> > > Neither does it appear to be a MySQL TIME field, since the maximum value
> > > for that would appear to be
>
> > > >>> ((838*60)+59)*60+59
>
> > > 3020399
>
> > > So, just what is this field? What do the values mean?
>
> > > regards
> > >  Steve
> > > --
> > > Steve Holden        +1 571 484 6266   +1 800 494 3119
> > > Holden Web LLC              http://www.holdenweb.com/
>
> > Bit hard to guess without the actual date to compare to... and I'm a
> > bit busy, but thought I'd throw this in the pool: I'm guessing it's a
> > MySQL database that's had data put into it via a .NET application
> > using the .NET DateTime (so a 20 byte int sounds about right IIRC),
> > which is based on the number of ticks since Jan 1, 1.... I think that
> > should end up around 2[18ish digits here...]...
>
> Mmmm ... I thought it might be the MS format that's ticks since
> 1600-01-01T00-00-00 (proleptic Gregorian calendar) where ticks happen
> 10,000,000 times per second, but:
>
> >>> x = 212099016004150509
> >>> seconds = x / 10000000.0
> >>> seconds
> 21209901600.415051
> >>> days = seconds / 60. / 60. / 24.
> >>> days
> 245484.97222702604
> >>> years_approx = days / 365.25
> >>> years_approx
>
> 672.10122444086528
>
> which would make it in the year 2272.
>
> Perhaps the OP could tell us what are the maximum and minimum non-zero
> non-null values he can find, and what years those might belong to.
> Also (very important) he might assure us that he is copying/pasting
> those large numbers, not retyp[o]ing them.

About .Net DateTime: the word from Mordor is "Time values are measured
in 100-nanosecond units called ticks, and a particular date is the
number of ticks since 12:00 midnight, January 1, 1 A.D. (C.E.) in the
GregorianCalendar calendar."

"12:00 midnight"??? Is this 0001-01-02T00:00:00 ??? In any case, this
would make the OP's bigint about 50 years after the Hegira ...
plausible only if his database is rather historical.