DateTime to bigint

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

DateTime to bigint

Tibor Balog
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DateTime to bigint

Paul Sanderson
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 <[hidden email]> 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
> [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: DateTime to bigint

Tibor Balog
Bow!

that's what I call 'service' !

I was looking for a good day for this and nothing habe poped up only for the
reverse task.

You made my day!

Thank You Sir.

-----Ursprüngliche Nachricht-----
From: Paul Sanderson
Sent: Friday, December 8, 2017 6:16 PM
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 <[hidden email]> 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
> [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
Reply | Threaded
Open this post in threaded view
|

Re: DateTime to bigint

Keith Medcalf
In reply to this post by Paul Sanderson

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-
>[hidden email]] 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 <[hidden email]> 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
>> [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
Reply | Threaded
Open this post in threaded view
|

Re: DateTime to bigint

Tibor Balog
Well,

coming from a strongly typed education this is too much freedom for me. -
Just kidding -

Thanks for the vital info, appreciated.

-----Ursprüngliche Nachricht-----
From: Keith Medcalf
Sent: Friday, December 8, 2017 7:14 PM
To: SQLite mailing list
Subject: Re: [sqlite] DateTime to bigint


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-
>[hidden email]] 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 <[hidden email]> 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
>> [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 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users