Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Update timestamp with percentage
Wed, Sep 5 2018 6:32 PMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Hi All,

I have a table x, containing a timestamp field (endtime). I want to
update endtime with percentage, like minus 10 procent, 12,5 procent etc
(float) for all records..

Something like:

update x set endtime = endtime - Tongueocent

Tried several casting options, but did not succeed yet. Any ideas how
this can be solved?
Thanks in advance Smile

Regards,
Hüseyin

Wed, Sep 5 2018 6:44 PMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Hüseyin,

A percentage of what? Can you give us an example with real values and what would you expect as a result?

--
Fernando Dias
[Team Elevate]
Thu, Sep 6 2018 2:27 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Hüseyin


I've just tried here with my old DBSys 4.25b5 and I can only CAST a timestamp into a DATE, TIME or VARCHAR and the only calculations I can carry out are + and - of integers (milliseconds).

However, as Fernando asks - a percentage of what? The restrictions above make sense since TIMESTAMP, DATE, TIME are simply markers. If, as I guess, you're using it to show elapsed time or duration then you are going to have problems

Roy Lambert
Thu, Sep 6 2018 3:30 AMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Hi Fernando/Roy,

Sorry not being clear. Let me see if i can explain better with some
examples..

I have starttime and endtime with following data:

Starttime                        Endtime
04-09-2018 10:00:00     04-09-2018 20:00:00
05-09-2018 12:00:00     05-09-2018 22:00:00
Endtime - starttime = app. 10 hours.

Let's say that i want to update endtime with 25% less. The results
should be:

Starttime                        Endtime
04-09-2018 10:00:00     04-09-2018 17:30:00
05-09-2018 12:00:00     05-09-2018 19:30:00
Endtime - starttime = app. 7,5 hours. (75% percentage of 10 hours)..

The percentage changes for every sql, so it's going to be a parameter in
the sql.
I hope it's more clear now Smile

Thanks.
Hüseyin

Den 06-09-2018 kl. 08:27 skrev Roy Lambert:
> Hüseyin
>
>
> I've just tried here with my old DBSys 4.25b5 and I can only CAST a timestamp into a DATE, TIME or VARCHAR and the only calculations I can carry out are + and - of integers (milliseconds).
>
> However, as Fernando asks - a percentage of what? The restrictions above make sense since TIMESTAMP, DATE, TIME are simply markers. If, as I guess, you're using it to show elapsed time or duration then you are going to have problems
>
> Roy Lambert
>
Thu, Sep 6 2018 4:51 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Hüseyin

Try

update x set endtime = starttime + CAST((endtime - starttime) * 0.75 AS INTEGER)

I don't know if it can be parameterised since I don't have anything to test it on, but if not simply have a placeholder for the percentage and generate the sql for each run

Roy Lambert
Thu, Sep 6 2018 7:02 AMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Hi Roy,

Thanks, working nice with the sql statement. I just need to get
percentage parameterised, then it's done Smile

Regards,
Hüseyin

Den 06-09-2018 kl. 10:51 skrev Roy Lambert:
> Hüseyin
>
> Try
>
> update x set endtime = starttime + CAST((endtime - starttime) * 0.75 AS INTEGER)
>
> I don't know if it can be parameterised since I don't have anything to test it on, but if not simply have a placeholder for the percentage and generate the sql for each run
>
> Roy Lambert
>
Thu, Sep 6 2018 8:26 AMPermanent Link

Huseyin Aliz

myBiss ApS

Avatar

Hi,

update x set endtime = starttime + CAST((endtime - starttime) * ((100 -
Tonguercentage) / 100) AS INTEGER)

works as expected Smile

Thanks again for your help.

Regards,
Hüseyin


Den 06-09-2018 kl. 13:02 skrev Hüseyin Aliz:
> Hi Roy,
>
> Thanks, working nice with the sql statement. I just need to get
> percentage parameterised, then it's done Smile
>
> Regards,
> Hüseyin
>
> Den 06-09-2018 kl. 10:51 skrev Roy Lambert:
>> Hüseyin
>>
>> Try
>>
>> update x set endtime = starttime + CAST((endtime - starttime) * 0.75
>> AS INTEGER)
>>
>> I don't know if it can be parameterised since I don't have anything
>> to test it on, but if not simply have a placeholder for the
>> percentage and generate the sql for each run
>>
>> Roy Lambert
>>
>
Image