That is:
UPDATE Table
SET Datum = (StrfTime('%s', Datum) + 62135596800) * 10000000
WHERE typeof(Datum) == 'text';
There is no such thing as a datatype of "bigint".
This declaration will give you a column affinity of "integer".
There is no such thing as a datatype of "DateTime".
This declaration will give you a column affinity of "numeric".
https://sqlite.org/datatype3.html
The TypeOf(...) function tells you the type of a dataitem. That is, the
particular data stored in a specific column of the current row. It says
nothing about the types of data stored in other columns in the same row, or in
the same column in other rows. Every dataitem is individually stored using of
the the supported types.
Results from TypeOf(...) may be 'null', 'real', 'integer', 'text' or 'blob' as
these are the only "data types" for a dataitem in SQLite3.
https://sqlite.org/lang_corefunc.html#typeof
The builtin datetime functions do not have any clue what to do with "rata die
huns" timestamp values so your application will, of course, have to handle them
itself. Or you will have to compute a "supported" value for use with the
builtins (IOS8601 text string *subset*, Unix Timestamp, Julianday number in the
Propeleptic Gregorian Calendar, for example). Additionally, the datetime
values returned by the "localtime"/"UTC" modifier(s) may only be correct for
'NOW' where the value of 'NOW' happens to be the current instant, if and only
if the computer is displaying the correct Zulu and Wallclock time of this
current instant, depending on the vagaries of time as implemented by your
platform C library, and such conversions for any time that is not the ever
continuous streams of the instant 'now' may be incorrect, again depending on
the platform C library.
---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Paul Sanderson
>Sent: Friday, 8 December, 2017 10:17
>To: SQLite mailing list
>Subject: Re: [sqlite] DateTime to bigint
>
>Hi Tibor
>
>Your date format is windows ticks, i.e. 100 nano seconds intervals
>since
>01/01/0001
>
>You can convert it as follows
>
>SELECT (StrfTime('%s', '2004-08-05') + 62135596800) * 10000000 AS
>Ticks
>
>where StrfTime('%s', '2004-08-05') is the number of seconds between
>the
>provided date and 1/1/1970
>62135596800 is the difference in seconds between 1/1/1970 and
>1/1/0001
>and 10000000 converts it to nanoseconds
>
>HTH
>
>Paul
>www.sandersonforensics.com
>skype: r3scue193
>twitter: @sandersonforens
>Tel +44 (0)1326 572786
>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-
>Toolkit
>-Forensic Toolkit for SQLite
>email from a work address for a fully functional demo licence
>
>On 8 December 2017 at 16:53, Tibor Balog <tibor.ba...@gmx.ch> wrote:
>
>> Hi there,
>>
>> I am very new to SQLite but like it sofar.
>> Run into something couldn’t find an answer may someone can put me
>in the
>> right direction..
>>
>> Have an application uses SQLite table with a column “Datum” defined
>as
>> bigint.
>> I have an identical table with column “Datum” as DateTime “yyyy-mm-
>dd”
>>
>> How can I convert this column -preferable in place- to bigint?
>>
>> Some more info:
>>
>> “2004-08-05” should be converted to “632272608000000000” .
>>
>> Thank You,
>> in advence.
>> (Tibor)
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
