Re: [sqlite] Ticks to unixepoch date

[Griggs, Donald]

Hi Roberto,

Re:  Please find a test database in the attachment.

I don't think attachments are permitted on the mailing list messages.

If it's only ten rows, perhaps just using the command line utility to perform a

.dump

Command and pasting the text into a new message would do the trick. 

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

Re: [sqlite] Ticks to unixepoch date

[Roberto Dalmonte]

Thanks for your answer.
Please find a test database in the attachment.
As you can see I have 1 table with 10 records, and the columns StartDate 
and EndDate represent a DateTime field, where the values are saved as ticks.
There is a view that tries to convert the ticks into unixepoch values, 
but I receive a floating point error.

Can you please take a look at it?
Best Regards
Roberto Dalmonte
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Black, Michael (IS)]

Can you give us the values in your table?
 
This works for me on 3.6.23.1
 
create table t (i integer);
insert into t values(634355968);
select datetime((i/100)-62135596800,'unixepoch') from t as expr1;
2011-03-13 07:06:40
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Roberto Dalmonte
Sent: Tue 7/13/2010 10:20 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Ticks to unixepoch date



Thanks for pointing that out Israel.
I had the right value (-62135596800) but I mistakenly pasted something else.
Nonetheless I get the following error:

invalid floating point operation
Any idea to make that statement work?
Best Regards
Roberto Dalmonte
Il 13/07/2010 16:46, Israel Lins Albuquerque ha scritto:
> Let me understand you...
>
> if tick is 0 the date id 0001/01/01 12:00:00?
> if yes the select do you want is
>
> SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1
>
>
> use that site for help!
> http://www.epochconverter.com/
>
>
>
> - "Roberto Dalmonte"<rob...@tiscali.it>  escreveu:
>   
>> In theory it should be possible to do it right now using the following
>> syntax ...
>>
>> SELECT datetime((columnAsTicks / 100) - 186796800,
>> 'unixepoch') AS Expr1
>> FROM Table
>>
>> ...unfortunately it doesn't work, at least the way I tried it.
>>
>> The operation is the following:
>> 1) transforms the ticks in seconds (divide ticks per 1 million);
>> 2) subtract the seconds passed from date 0001/01/01 (starting date in
>> System.DateTime) to 1970/1/1 (starting date in Unixepoch)
>> 3) Use the result with the built-in SQLite function datetime.
>>
>> This way you could build a view and be able to see a normal date instead
>> of ticks.
>>
>> Any idea?
>> Roberto
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Ticks to unixepoch date

[Roberto Dalmonte]

Thanks for pointing that out Israel.
I had the right value (-62135596800) but I mistakenly pasted something else.
Nonetheless I get the following error:

invalid floating point operation
Any idea to make that statement work?
Best Regards
Roberto Dalmonte
Il 13/07/2010 16:46, Israel Lins Albuquerque ha scritto:
> Let me understand you...
>
> if tick is 0 the date id 0001/01/01 12:00:00?
> if yes the select do you want is
>
> SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1
>
>
> use that site for help!
> http://www.epochconverter.com/
>
>
>
> - "Roberto Dalmonte"  escreveu:
>
>> In theory it should be possible to do it right now using the following
>> syntax ...
>>
>> SELECT datetime((columnAsTicks / 100) - 186796800,
>> 'unixepoch') AS Expr1
>> FROM Table
>>
>> ...unfortunately it doesn't work, at least the way I tried it.
>>
>> The operation is the following:
>> 1) transforms the ticks in seconds (divide ticks per 1 million);
>> 2) subtract the seconds passed from date 0001/01/01 (starting date in
>> System.DateTime) to 1970/1/1 (starting date in Unixepoch)
>> 3) Use the result with the built-in SQLite function datetime.
>>
>> This way you could build a view and be able to see a normal date instead
>> of ticks.
>>
>> Any idea?
>> Roberto
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ticks to unixepoch date

[Israel Lins Albuquerque]

Let me understand you... 

if tick is 0 the date id 0001/01/01 12:00:00? 
if yes the select do you want is 

SELECT datetime((TICK_VALUE / 100) - 62135553600, 'unixepoch') AS Expr1 


use that site for help! 
http://www.epochconverter.com/ 



- "Roberto Dalmonte"  escreveu: 
> In theory it should be possible to do it right now using the following 
> syntax ... 
> 
> SELECT datetime((columnAsTicks / 100) - 186796800, 
> 'unixepoch') AS Expr1 
> FROM Table 
> 
> ...unfortunately it doesn't work, at least the way I tried it. 
> 
> The operation is the following: 
> 1) transforms the ticks in seconds (divide ticks per 1 million); 
> 2) subtract the seconds passed from date 0001/01/01 (starting date in 
> System.DateTime) to 1970/1/1 (starting date in Unixepoch) 
> 3) Use the result with the built-in SQLite function datetime. 
> 
> This way you could build a view and be able to see a normal date instead 
> of ticks. 
> 
> Any idea? 
> Roberto 
> ___ 
> sqlite-users mailing list 
> sqlite-users@sqlite.org 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 
> 

-- 

Atenciosamente/Regards, 

Israel Lins Albuquerque 
Desenvolvimento/Development 
Polibrás Brasil Software Ltda. 


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

[sqlite] Ticks to unixepoch date

[Roberto Dalmonte]

In theory it should be possible to do it right now using the following 
syntax ...

SELECTdatetime((columnAsTicks / 100) - 186796800, 
'unixepoch') AS Expr1
FROMTable

...unfortunately it doesn't work, at least the way I tried it.

The operation is the following:
1) transforms the ticks in seconds (divide ticks per 1 million);
2) subtract the seconds passed from date  0001/01/01 (starting date in 
System.DateTime) to 1970/1/1 (starting date in Unixepoch)
3) Use the result with the built-in SQLite function datetime.

This way you could build a view and be able to see a normal date instead 
of ticks.

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