1 Topic by jrga (edited by jrga 2023-12-21 14:33:59)

  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Topic: calculate the time elapsed in a calculated field

There may be an easier way to do this, but that’s what I was able to create and it works! 


(select PRINTF("AMD: %02d %02d %02d", ano,mes,dia) from (
     select id,        
     case when conclusao is null then round((julianday("now") - julianday(inclusao))/365.25-0.5) else 
     round((julianday("now") - julianday(conclusao))/365.25-0.5) end as ano, 

     case when conclusao is null then round((julianday("now") - julianday(inclusao)- round((julianday("now") 
     - julianday(inclusao))/365.25-0.5)*365.25)/30.41-0.5) else round((julianday("now") - julianday(conclusao)- 
     round((julianday("now") - julianday(conclusao))/365.25-0.5)*365.25)/30.41-0.5) end as mes,  

     case when conclusao is null then round(round((julianday("now") - julianday(inclusao)- 
     round((julianday("now") - julianday(inclusao))/365.25-0.5)*365.25))- round((julianday("now") -  
     julianday(inclusao)- round((julianday("now") - julianday(inclusao))/365.25-0.5)*365.25)/30.41-0.5)*30.41-0.5) else  
     round(round((julianday("now") - julianday(conclusao)- round((julianday("now") -  
     julianday(conclusao))/365.25-0.5)*365.25))- round((julianday("now") - julianday(conclusao)-   
     round((julianday("now") - julianday(conclusao))/365.25-0.5)*365.25)/30.41-0.5)*30.41-0.5) end  as dia

     from tbl_subtasks) where id = tbl_subtasks.id)
Post's attachments

Attachment icon tela.jpg 154.76 kb, 13 downloads since 2023-12-21 

Roberto Alencar

2 Reply by sparrow (edited by sparrow 2023-12-22 10:13:56)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: calculate the time elapsed in a calculated field

Hi,
You can try it like this


select PRINTF('AMD: %02d %02d %02d',
                        floor( alldays / 365.25 ),
                        floor( mod(alldays, 365.25) / 30.4375 ),
                        floor( mod( mod(alldays, 365.25), 30.4375) ) )
from (select 
    (julianday('now','localtime') - julianday('2022-10-13')) as alldays  ) as t;

P.S.
Sorry, I didn't check right away.
For a version of SQLite in a program where some functions are not supported, the query is:

select PRINTF('AMD: %02d %02d %02d',
                         alldays / 365250 ,
                         (alldays % 365250) / 30437 ,
                         ( (alldays % 365250) % 30437)/1000 )
from (select 
    (julianday('now','localtime') - julianday('2022-09-03'))*1000 as alldays  ) as t;
  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: calculate the time elapsed in a calculated field

sparrow wrote:

Hi,
You can try it like this


select PRINTF('AMD: %02d %02d %02d',
                        floor( alldays / 365.25 ),
                        floor( mod(alldays, 365.25) / 30.4375 ),
                        floor( mod( mod(alldays, 365.25), 30.4375) ) )
from (select 
    (julianday('now','localtime') - julianday('2022-10-13')) as alldays  ) as t;

P.S.
Sorry, I didn't check right away.
For a version of SQLite in a program where some functions are not supported, the query is:

select PRINTF('AMD: %02d %02d %02d',
                         alldays / 365250 ,
                         (alldays % 365250) / 30437 ,
                         ( (alldays % 365250) % 30437)/1000 )
from (select 
    (julianday('now','localtime') - julianday('2022-09-03'))*1000 as alldays  ) as t;

Hi Sparrow, thanks for the suggestion. In fact, your instruction is much less complex than the one I showed you. However, my version of MVD is 5.6 and it does not support the "floor" and "mod" functions. I asked Bing's AI, who suggested replacing these two functions with "Castas...integer" and "%". Below is the instruction with these substitutions. Everything works and thanks again.

(select PRINTF("AMD: %02d %02d %02d",
                        cast((alldays / 365.25) as integer),
                        cast(((alldays % 365.25) / 30.4375) as integer),
                        cast((((alldays % 365.25) % 30.4375)) as integer))
from (select id,
     (case when conclusao is null then julianday('now','localtime') - julianday(inclusao) else 
     julianday('now','localtime') - julianday(conclusao) end) as alldays from tbl_subtasks) where id  =  tbl_subtasks.id)
Roberto Alencar

4 Reply by sparrow (edited by sparrow 2023-12-22 20:54:39)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: calculate the time elapsed in a calculated field

Roberto, use my second example without FLOOR and MOD.


select PRINTF('AMD: %02d %02d %02d',
                         alldays / 365250 ,
                         (alldays % 365250) / 30437 ,
                         ( (alldays % 365250) % 30437)/1000 )
from (select 
    (julianday('now','localtime') - julianday('2022-12-23'))*1000 as alldays  ) as t;

You can't just change '%' instead of MOD,
using '%' instead of MOD has its disadvantages.
Therefore, we operate with integers.
Look at the results, false and true:

select PRINTF('AMD: %02d %02d %02d',
                        cast((alldays / 365.25) as integer),
                        cast(((alldays % 365.25) / 30.4375) as integer),
                        cast((((alldays % 365.25) % 30.4375)) as integer))    
from (select 
    (julianday('now','localtime') - julianday('2022-12-23')) as alldays  ) as t;

AMD: 00 11 04  < -- FALSE

select PRINTF('AMD: %02d %02d %02d',
                         alldays / 365250 ,
                         (alldays % 365250) / 30437 ,
                         ( (alldays % 365250) % 30437)/1000 )
from (select 
    (julianday('now','localtime') - julianday('2022-12-23'))*1000 as alldays  ) as t;


AMD: 00 11 30 < -- TRUE
  • jrga
  • Member
  • Offline
  • From: Brasil
  • Registered: 2015-10-26
  • Posts: 127

Re: calculate the time elapsed in a calculated field

Sparrow, I made the correction. Thanks!

Roberto Alencar