Written by

Ramil TK

Question Ramil TK · Aug 3, 2022

Working With Date and Time

#InterSystems IRIS #Databases #SQL

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)0

Add reply

Comments

Yaron Munz · Aug 3, 2022

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))

0 0

Robert Cemper Aug 3, 2022 to Yaron Munz

3. This might be easier to follow

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

0 0

Eduard Lebedyuk Aug 3, 2022 to Robert Cemper

Even easier with DATEDIFF:

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

0 0

Robert Cemper Aug 4, 2022 to Eduard Lebedyuk

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

Yaron Munz Aug 4, 2022 to Robert Cemper

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

0 0

Ramil TK Aug 4, 2022 to Yaron Munz

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.

0 0

Ramil TK · Aug 4, 2022

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

0 0

Eduard Lebedyuk Aug 4, 2022 to Ramil TK

Divide by 365.25 to account for leap years.

0 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

0 0

Ramil TK Aug 4, 2022 to Vitaliy Serdtsev

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.

0 0

Vitaliy Serdtsev Aug 4, 2022 to Ramil TK

A simple example of combining:

<FONT COLOR="#000080">Class dc.test Extends %Persistent
</FONT><FONT COLOR="#000000">{
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">DOB </FONT><FONT COLOR="#000080">As %Date</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">DOBTime </FONT><FONT COLOR="#000080">As %Time</FONT><FONT COLOR="#000000">;
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">()
  
  </FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">DOB</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">DOBTime</FONT><FONT COLOR="#000000">)
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2020-01-01'</FONT><FONT COLOR="#000000">},{</FONT><FONT COLOR="#000080">t </FONT><FONT COLOR="#008080">'23:59:59'</FONT><FONT COLOR="#000000">}
    </FONT><FONT COLOR="#000080">union all
    </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#000000">{</FONT><FONT COLOR="#000080">d </FONT><FONT COLOR="#008080">'2022-12-31'</FONT><FONT COLOR="#000000">},{</FONT><FONT COLOR="#000080">t </FONT><FONT COLOR="#008080">'10:10:10'</FONT><FONT COLOR="#000000">}</FONT><FONT COLOR="#800080">)
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%New</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%SelectMode</FONT><FONT COLOR="#000000">=2
  
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">=5
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(1)=</FONT><FONT COLOR="#008000">"select *"
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(2)=</FONT><FONT COLOR="#008000">",%odbcout(DOB)||' '||%odbcout(DOBTime) DOBString"
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(3)=</FONT><FONT COLOR="#008000">",CAST(%odbcout(DOB)||' '||%odbcout(DOBTime) as DATETIME) DOBDateTime"
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(4)=</FONT><FONT COLOR="#008000">",UNIX_TIMESTAMP(DOB||','||DOBTime) DOBUnixTimeStamp"
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">(5)=</FONT><FONT COLOR="#008000">"from dc.test"
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(.</FONT><FONT COLOR="#800000">st</FONT><FONT COLOR="#000000">,.</FONT><FONT COLOR="#800000">sql</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">()
}
}</FONT>

Result:

USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">dc.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT>

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

0 0

Ramil TK Aug 4, 2022 to Vitaliy Serdtsev

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

0 0

Vitaliy Serdtsev Aug 4, 2022 to Ramil TK

..
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

Eduard Lebedyuk Aug 4, 2022 to Vitaliy Serdtsev

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?

0 0

Vitaliy Serdtsev Aug 4, 2022 to Eduard Lebedyuk

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

0 0

Eduard Lebedyuk Aug 4, 2022 to Vitaliy Serdtsev

Thanks!

0 0

Vitaliy Serdtsev · Aug 4, 2022

I like @Yaron Munz version better

<FONT COLOR="#000080">Class dc.test </FONT><FONT COLOR="#000000">[ </FONT><FONT COLOR="#000080">Abstract </FONT><FONT COLOR="#000000">]
{
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">=0,
    </FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$i</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">))=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"2021-11-27"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"2022-08-04"</FONT><FONT COLOR="#000000">),
    </FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$i</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">))=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"2020-02-28"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"2023-02-27"</FONT><FONT COLOR="#000000">),
    </FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$i</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">))=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"2020-02-28"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"2023-03-01"</FONT><FONT COLOR="#000000">),
    </FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$i</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">))=</FONT><FONT COLOR="#0000ff">$lb</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">"2017-03-01"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"2020-02-28"</FONT><FONT COLOR="#000000">)
    
  </FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">=1:1:</FONT><FONT COLOR="#0000ff">$o</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">""</FONT><FONT COLOR="#000000">),-1) </FONT><FONT COLOR="#800080">{
    </FONT><FONT COLOR="#0000ff">s </FONT><FONT COLOR="#800000">d1 </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$li</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">),1),
      </FONT><FONT COLOR="#800000">d2 </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$li</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">v</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">i</FONT><FONT COLOR="#000000">),2),
      </FONT><FONT COLOR="#800000">age1 </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$e</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d2</FONT><FONT COLOR="#000000">,1,4)-</FONT><FONT COLOR="#0000ff">$e</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d1</FONT><FONT COLOR="#000000">,1,4)-(</FONT><FONT COLOR="#0000ff">$e</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d1</FONT><FONT COLOR="#000000">,6,10)]</FONT><FONT COLOR="#0000ff">$e</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d2</FONT><FONT COLOR="#000000">,6,10)),
      </FONT><FONT COLOR="#800000">age2 </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$number</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$zabs</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$zdh</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d2</FONT><FONT COLOR="#000000">,3)-</FONT><FONT COLOR="#0000ff">$zdh</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d1</FONT><FONT COLOR="#000000">,3))/365.25,0),
      </FONT><FONT COLOR="#800000">age3 </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#0000ff">$zdh</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d2</FONT><FONT COLOR="#000000">,3)-</FONT><FONT COLOR="#0000ff">$zdh</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#800000">d1</FONT><FONT COLOR="#000000">,3)\365.25
      
    </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#800000">d1</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" "</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">d2</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">" (age1 = "</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">age1</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">", age2 = "</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">age2</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">", age3 = "</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">age3</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">")"</FONT><FONT COLOR="#000000">,!
  </FONT><FONT COLOR="#800080">}
</FONT><FONT COLOR="#000000">}
}</FONT>

Result:

USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">dc.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT>
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