Working With Date and Time

Question

Question

Ramil TK · Aug 3, 2022

#Databases #SQL #InterSystems IRIS

Kindly help me on the below points

1)Concatenate the Date column and Time column.

2)Get the current date only without time.

3)Find the current age using DOB column.

Product version: IRIS 2022.1

Discussion (17)1

Log in or sign up to continue

Robert Cemper · Aug 4, 2022

DATEDIFF only compares the YEAR numbers. So:
write $system.SQL.DATEDIFF("yy",$h-250,+$h) >>>> 1
because of comparing 2021 to 2022
but it definitely is less than 1 year
and it depends on the actual day of the year

0 0

Eduard Lebedyuk · Aug 4, 2022

Divide by 365.25 to account for leap years.

1 0

Vitaliy Serdtsev · Aug 4, 2022

Instead of two separate fields, it would be easier to use the %TimeStamp (or %PosixTime) type, where the date and time are paired at once.
For this type of data, you can make your own indexes for different parts and/or combinations of them: a separate date, a separate time, a separate year, a separate year and month, etc.
You can also use these separate parts in the query (in SELECT and WHERE).

select datediff('yy',DOB,current_date) Age from yourtable

1 0

Vitaliy Serdtsev · Aug 4, 2022

..
s sql=0
s sql($i(sql))="select *"
s sql($i(sql))=",%external(DOB)||' '||%external(DOBTime) DOBString"
s sql($i(sql))=",TO_CHAR(DOB||','||DOBTime,'MM/DD/YYYY HH24:MI:SS') DOBString2"
s sql($i(sql))="from dc.test"
d st.%ExecDirect(.st,.sql).%Display()
..

Result:

USER>d ##class(dc.test).Test()




ID
(INTEGER)
DOB
(DATE)
DOBTime
(TIME)
DOBString
(VARCHAR)
DOBString2
(VARCHAR)


1
01.01.2020
23:59:59
01.01.2020 23:59:59
01/01/2020 23:59:59


2
31.12.2022
10:10:10
31.12.2022 10:10:10
12/31/2022 10:10:10

0 0

Vitaliy Serdtsev · Aug 4, 2022

I like @Yaron Munz version better

Class dc.test [ Abstract ]
{

ClassMethod Test()
{
  s v=0,
    v($i(v))=$lb("2021-11-27","2022-08-04"),
    v($i(v))=$lb("2020-02-28","2023-02-27"),
    v($i(v))=$lb("2020-02-28","2023-03-01"),
    v($i(v))=$lb("2017-03-01","2020-02-28")
    
  f i=1:1:$o(v(""),-1) {
    s d1 = $li(v(i),1),
      d2 = $li(v(i),2),
      age1 = $e(d2,1,4)-$e(d1,1,4)-($e(d1,6,10)]$e(d2,6,10)),
      age2 = $number($zabs($zdh(d2,3)-$zdh(d1,3))/365.25,0),
      age3 = $zdh(d2,3)-$zdh(d1,3)\365.25
      
    w d1," ",d2," (age1 = ",age1,", age2 = ",age2,", age3 = ",age3,")",!
  }
}

}

Result:

USER>d ##class(dc.test).Test()
2021-11-27 2022-08-04 (age1 = 0, age2 = 1, age3 = 0)
2020-02-28 2023-02-27 (age1 = 2, age2 = 3, age3 = 2)
2020-02-28 2023-03-01 (age1 = 3, age2 = 3, age3 = 3)
2017-03-01 2020-02-28 (age1 = 2, age2 = 3, age3 = 2)

0 0

score 0 · Answer 1 · 2022-08-03T08:43:54-04:00

Hello,

1. If you mean to Concatenate in SQL than you can use the CONCAT function

2. To get the current date you may use the $ZDATE function (cos) pass the 1st parameter +$H and it will be todays date.

3. To find the DOB based on a date :
Set Age = $P($ZD(+$h,3),"-")-$P($ZD(Dob,3),"-")-($E($ZD(+$H,8)5,8)<$E($ZD(Dob,8)5,8))

score 0 · Answer 2 · 2022-08-03T10:11:18-04:00

Robert Cemper · Aug 3, 2022

3. This might be easier to follow

 Set Age=$NUMBER($ZABS($h-Dob)/365,0)

0 0

score 0 · Answer 3 · 2022-08-03T12:01:12-04:00

Eduard Lebedyuk · Aug 3, 2022

Even easier with DATEDIFF:

set age = $SYSTEM.SQL.Functions.DATEDIFF("year", dob, $h)

0 0

score 0 · Answer 4 · 2022-08-04T04:12:54-04:00

This is less accurate, Robert. on Leap years it will get "rounded" just a few days before birthday.
A workaround this is to divide by 365.25 to get more accurate age

score 0 · Answer 5 · 2022-08-04T00:49:21-04:00

1)I am trying to Concatenate the 2 columns together, like bill date is in one column and bill time is in another column. I want to combine this both column and need to display as date and time using query.

2) I am trying to get all the above using sql query.

score 0 · Answer 6 · 2022-08-04T00:55:22-04:00

select ROUND((date(NOW()) - date(DOB)) / 365,0) age,date(NOW()) from user

Using the above query me solved the point no 2 and 3, but point no 1 still i don't get any solution.

If any correction or suggestion in above query please correct me.

@Yaron Munz @Robert Cemper @Eduard Lebedyuk

score 0 · Answer 7 · 2022-08-04T01:50:47-04:00

Thank you for your replay and this one also working fine.

This is already using one solution in that date and time are in a separate columns.

score 1 · Answer 8 · 2022-08-04T03:20:03-04:00

A simple example of combining:

Class dc.test Extends %Persistent
{

Property DOB As %Date;

Property DOBTime As %Time;

ClassMethod Test()
{
  d ..%KillExtent()
  
  &sql(insert into dc.test(DOB,DOBTime)
    select {d '2020-01-01'},{t '23:59:59'}
    union all
    select {d '2022-12-31'},{t '10:10:10'})
  
  s st=##class(%SQL.Statement).%New()
  s st.%SelectMode=2
  
  s sql=5
  s sql(1)="select *"
  s sql(2)=",%odbcout(DOB)||' '||%odbcout(DOBTime) DOBString"
  s sql(3)=",CAST(%odbcout(DOB)||' '||%odbcout(DOBTime) as DATETIME) DOBDateTime"
  s sql(4)=",UNIX_TIMESTAMP(DOB||','||DOBTime) DOBUnixTimeStamp"
  s sql(5)="from dc.test"
  d st.%ExecDirect(.st,.sql).%Display()
}

}

Result:

USER>d ##class(dc.test).Test()




ID
(INTEGER)
DOB
(DATE)
DOBTime
(TIME)
DOBString
(VARCHAR)
DOBDateTime
(TIMESTAMP)
DOBUnixTimeStamp
(NUMERIC)


1
01.01.2020
23:59:59
2020-01-01 23:59:59
2020-01-01 23:59:59
1577923199.00


2
31.12.2022
10:10:10
2022-12-31 10:10:10
2022-12-31 10:10:10
1672481410.00


2 Rows(s) Affected

score 0 · Answer 9 · 2022-08-04T04:55:39-04:00

Thank you, and one more question in this.

How we can format the date string like DD/MM/YYYY HH:MM:SS like this or any other format

score 0 · Answer 10 · 2022-08-04T01:52:27-04:00

For this type of data, you can make your own indexes for different parts and/or combinations of them: a separate date, a separate time, a separate year, a separate year and month, etc.

Could you elaborate on that, please?

score 1 · Answer 11 · 2022-08-04T03:25:41-04:00

Vitaliy Serdtsev · Aug 4, 2022

See my old article Indexing of non-atomic attributes, chapter "Date (time, etc.)"

1 0

score 0 · Answer 12 · 2022-08-04T03:39:52-04:00

Eduard Lebedyuk · Aug 4, 2022

Thanks!

0 0

ID (INTEGER)	DOB (DATE)	DOBTime (TIME)	DOBString (VARCHAR)	DOBDateTime (TIMESTAMP)	DOBUnixTimeStamp (NUMERIC)
1	01.01.2020	23:59:59	2020-01-01 23:59:59	2020-01-01 23:59:59	1577923199.00
2	31.12.2022	10:10:10	2022-12-31 10:10:10	2022-12-31 10:10:10	1672481410.00