Date Time Manipulation

[Capoerista]

Hi All,

Is it possible to do date/time manipulation easily on DATETIME YEAR TO SECOND variables ? (4GL 7.20.UE1, SE 7.24.UC8). I tried the following

main
define
v_today DATETIME YEAR TO SECOND,
v_yest DATETIME YEAR TO SECOND,

v_diff float

LET v_today = today

LET v_today = "2004-01-22 23:45:00"
LET v_yest = "2004-01-16 10:30:00"
LET v_diff = v_today - v_yest

display v_diff

end main

which gives a cryptic result of 2.12e-314. Changing the variables/assignments to DATE type gives a result of 6 which is good. However I need to go down to hour/min/sec level.



Thanks in advance

Ade

 

[olded]

Ade:

Use an informix interval, when subtracting two datetimes. The float won't work. You might find the following 4GL functions usable. Just change datetime structure to your choice.

Regards,

Ed


main
define dt_in, dt_out, dt_test datetime year to fraction(5),
dinter interval day to minute,
date_in, date_out, ddate date,
time_in, time_out, ttime char(4),
tot_min integer,
tot_hrs decimal(10,2),
err_found smallint

whenever error continue
let date_in = "11/12/94"
let time_in = "1330"
let date_out = "11/13/94"
let time_out = "1300"

call date_to_int(date_in, time_in) returning dt_in, err_found
call date_to_int(date_out, time_out) returning dt_out, err_found
let dt_test = current
let dinter = dt_out - dt_in

let tot_min = return_min(dinter)
display "Total minutes between in and out: ", tot_min

let tot_hrs = return_hr(dinter)
display "Total hours between in and out: ", tot_hrs

call int_to_date(dt_in) returning ddate, ttime, err_found
display "Date is ", ddate
display "Time is ", ttime

end main

###########################################################################
function date_to_str(dd)
###########################################################################
# This function takes a date type and returns a string of the type:
# yyyy-mm-dd

define dd date,
bufyy, bufmm, bufdd smallint,
daystr char(14)

let bufyy = year(dd)
let bufmm = month(dd)
let bufdd = day(dd)

let daystr = bufyy using "####", "-"
let daystr = daystr clipped, bufmm using "##", "-"
let daystr = daystr clipped, bufdd using "##"

if daystr is null
then
let daystr = ""
end if

return daystr
end function
# date_to_str(dd)

###########################################################################
function return_min(dinter)
###########################################################################
# This function takes an interval string and then returns the number of
# minutes. This is only works if the interval is defined day-to-minute

define dinter interval day to minute,
dstring char(14),
dday char(4),
dhr char(2),
dmin char(2),
aday smallint,
ahr smallint,
amin smallint,
totmin integer

let dstring = dinter
#do the day
let dday[1] = dstring[1]
let dday[2] = dstring[2]
let dday[3] = dstring[3]
#do the time
let dhr[1] = dstring[5]
let dhr[2] = dstring[6]
let dmin[1] = dstring[8]
let dmin[2] = dstring[9]

#change data type
let aday = dday
let ahr = dhr
let amin = dmin

#if negative interval
if dday[1] = "-" or
dday[2] = "-" or
dday[3] = "-"
then
let ahr = ahr * (-1)
let amin = amin * (-1)
end if
let totmin = (aday * 24 * 60) + (ahr * 60) + amin

if totmin is null
then
let totmin = 0
end if

return totmin
end function
# return_min(dinter)

###########################################################################
function return_hr(dinter)
###########################################################################
# This function takes an interval string and then returns the number of
# hours. This is only works if the interval is defined day-to-minute

define dinter interval day to minute,
dstring char(14),
dday char(4),
dhr char(2),
dmin char(2),
aday smallint,
ahr smallint,
amin smallint,
tothr decimal(10)

let dstring = dinter
#do the day
let dday[1] = dstring[1]
let dday[2] = dstring[2]
let dday[3] = dstring[3]
#do the time
let dhr[1] = dstring[5]
let dhr[2] = dstring[6]
let dmin[1] = dstring[8]
let dmin[2] = dstring[9]

#change data type
let aday = dday
let ahr = dhr
let amin = dmin

#if negative interval
if dday[1] = "-" or
dday[2] = "-" or
dday[3] = "-"
then
let ahr = ahr * (-1)
let amin = amin * (-1)
end if
let tothr = (aday * 24) + ahr + (amin/60.0)

if tothr is null
then
let tothr = 0.0
end if

return tothr
end function
# return_hr(dinter)

###########################################################################
function date_to_int(dd,ss)
###########################################################################
# This function takes a date and time string and returns an informix date
# time year to minute. For error checking, if the date or time is bad,
# typically null, return true else false if conversion was good
# this was modified from base datetime conversion which was datetime
# year to minute to handle datetime year to fraction(5)
define dd date,
ss char(4),
retstr datetime year to fraction(5),
bufstr char(25),
daystr char(14),
time_buf char(5)

let retstr = current

if dd is null or dd = 0
then #date error
return retstr, true
end if

if not ck_punch(ss)
then
return retstr, true
end if

#do the time
let time_buf[1] = ss[1]
let time_buf[2] = ss[2]
let time_buf[3] = ":"
let time_buf[4] = ss[3]
let time_buf[5] = ss[4]
#do the date
let daystr = date_to_str(dd)

#do the date string
let bufstr = daystr clipped, " ", time_buf clipped,
":00.00000"
let retstr = bufstr

return retstr,false
end function
# date_to_int(dd,ss)

###########################################################################
function secs_to_int(dd,ss)
###########################################################################
# This function takes a date and time string and returns an informix date
# time year to fraction(5). For error checking, if the date or time is bad,
# typically null, return true else false if conversion was good

define dd date,
ss char(6),
retstr datetime year to fraction(5),
bufstr char(25),
daystr char(14),
time_buf char(8)

let retstr = current

if dd is null or dd = 0
then #date error
return retstr, true
end if

if not ck_punch(ss)
then
return retstr, true
end if

#do the time
let time_buf[1] = ss[1]
let time_buf[2] = ss[2]
let time_buf[3] = ":"
let time_buf[4] = ss[3]
let time_buf[5] = ss[4]
let time_buf[6] = ":"
let time_buf[7] = ss[5]
let time_buf[8] = ss[6]
#do the date
let daystr = date_to_str(dd)

#do the date string
let bufstr = daystr clipped, " ", time_buf clipped,
".00000"
let retstr = bufstr

return retstr,false
end function
# secs_to_int(dd,ss)

###########################################################################
function int_to_date(p_date)
###########################################################################
# Receives a datetime variable and returns a date and char(4) with time

define p_date datetime year to fraction(5),
r_date date,
date_str char(25),
r_time char(4)

if p_date is null
then
let r_date = null
let r_time = "0000"
return r_date,r_time, 1
end if

let r_date = mdy(month(p_date),day(p_date),year(p_date))
let date_str = p_date

let r_time[1] = date_str[12]
let r_time[2] = date_str[13]
let r_time[3] = date_str[15]
let r_time[4] = date_str[16]

return r_date,r_time, 0
end function
# int_to_date(p_date)

############################################################
function dt2char(dt)
############################################################
# Takes a datetime year to minute
# RETURNS a char(13) in the format "mm/dd/yy hhmm"

define
dt char(20),
ret_dt char(13)

if dt is null or dt = " "
then
return " "
else
let ret_dt= dt[6,7],"/",dt[9,10],"/",dt[3,4], " ", dt[12,13], dt[15,16]
end if

return ret_dt
end function


#############################################################################
function dtaddhrs(dt, hrs)
#############################################################################
# RETURNS: dt - The datetime value (dt) after adding hours (hrs) to it.
#############################################################################
# Used to add hours (consisting of hours and a fraction of hours) to
# a given datetime value and return the result to the calling function.
#
# [
define
dt datetime year to fraction(5), # Datetime value to add to
hrs decimal(8,2), # Hours to add to datetime value
mins smallint # Minutes conversion factor

let mins = (hrs * 60)
let dt = dt + mins units minute

return dt

# ]
end function
# dtaddhrs()

##############################################################################
function rnd_hr(dinter, rnd_factor, rnd_type)
##############################################################################
# RETURNS: rhrs - rounded hours.
##############################################################################
# Used to determine hours adjustments based on the rounding rules (so we
# don't get system or even manual adjustments to the 100ths if a rounding
# type other than 1 minute is used).

# [
define dinter interval day to fraction(5), # Interval to convert
rnd_factor smallint, # Like trf.rnd_type
# (1, 6 minute rounding, etc.)
rnd_type char(1), # Like trf.in_rnd/out_rnd
# (Method, "D", "U", "N&quot
mins smallint, # Minutes of the interval to round
rhrs decimal(8,2), # Converted rounded hours
xi smallint

let mins = return_min(dinter)
let xi = mins mod rnd_factor

# Check the rounding type
case
when rnd_type = "D"
# Round to prev factor.
let dinter = dinter - xi units minute
when rnd_type = "U"
# Round to next factor.
let dinter = dinter - xi units minute + rnd_factor units minute
otherwise
# Normal rounding
if (xi * 2) >= rnd_factor
then
let dinter = dinter - xi units minute + rnd_factor units minute
else
let dinter = dinter - xi units minute
end if
end case

let rhrs = return_hr(dinter)

return rhrs

# ]
end function
# rnd_hr()

############################################################################
function yr_of_date(din, digs)
############################################################################
# RETURNS: yr - year of the given date value (din). -1 indicates an error.
############################################################################
# Used to extract the year from a date value.
#
# [
define
din date, # A value of null specifies "today" as the date
digs smallint, # Digits of precision (2 or 4--default is 2)
dstr char(20), # Date time string value
yrstr char(4), # Date time string value
yr smallint # The extracted year

if din is null
then
let din = today
end if

# Default to current year
if digs = 4
then
let dstr = extend(din, year to month)
let yrstr = dstr[1,4]
else
let dstr = din
let yrstr = dstr[7,8]
end if
let yr = yrstr

if yr is null then let yr = -1 end if

return yr

# ]
end function
# yr_of_date()

######################################################################
function ck_punch(f_time)
######################################################################
# This function returns true if the input value of f_time
# is in the correct hhmm format and doesn't exceed 2359
# returning 0 or -1

define f_time char(4),
m1, m2, m3, m4 char(1),
i1, i2, i3, i4 smallint,
hrs, mns smallint

let m1 = f_time[1]
let m2 = f_time[2]
let m3 = f_time[3]
let m4 = f_time[4]

#check each diget being numeric
if not ck_diget(m1) or not ck_diget(m2) or not ck_diget(m3) or
not ck_diget(m4)
then
return false
end if

let i1 = m1
let i2 = m2
let i3 = m3
let i4 = m4

let hrs = (i1 * 10) + i2
let mns = (i3 * 10) + i4

if hrs > 23 or mns > 59
then
return false
else
return true
end if

end function
# ck_punch(f_time)

#########################################################
function ck_diget(cc)
#########################################################
define cc char(1)

if cc is null
then
return false
end if

if cc = "0" or cc = "1" or cc = "2" or cc = "3" or cc = "4"
or cc = "5" or cc = "6" or cc = "7" or cc = "8" or cc = "9"
then
return true
else
return false
end if

end function
# ck_diget(cc)