-2

I need a help. How to convert timestamp to minute in sql query? Really appreciate it. Below are my table. Here is my timestamp table field. Need to convert it in minute to get time duration

Community
  • 1
  • 1
kent
  • 1
  • 1

2 Answers2

0
Select DATEPART(mi, changedate) AS minute from Table_01

Hope this will help you

0

What do you mean with "convert"? It is always helpful to give an example.

There are lots of functions in Db2 - very helpful new ones in Db2 11.1 - check out (depending on your goal):

  • date_trunc ('minutes', current timestamp) as minutes
  • date_part('minutes', current timestamp) as date_part_minutes
  • minutes_between('01.03.2016', '01.01.1970') as minutes_between
MichaelTiefenbacher
  • 3,805
  • 2
  • 11
  • 17
  • I need output for duration in minute. For example my data timestamp column 1 is 2018-01-12-19.44.33.336000 and column 2 is 2018-01-12-11.54.33.042000. So i need to subtract between 2 timestamp and need in minute format. Please guide me. Really appreciate it sir. – kent Jan 25 '18 at 06:49
  • SELECT ((SELECT EXTRACT (HOUR FROM WOSTATUS.CHANGEDATE) FROM WOSTATUS WHERE WOSTATUS.STATUS = 'COMP' AND WONUM=WORKORDER.WONUM) - (SELECT EXTRACT (HOUR FROM WOSTATUS.CHANGEDATE) FROM WOSTATUS WHERE WOSTATUS.STATUS = 'ACCEPT' AND WONUM=WORKORDER.WONUM)) AS WORKDURATION - currently that is my query. I get the ouput but in Hour. I need the duration in minute for total working. I appreciate your help sir. Kindly Regards. – kent Jan 25 '18 at 06:58
  • What other date/time related columns are in table WOSTATUS.CHANGEDATE? Your code is *only* selecting the HOUR column . Does that mean an hour number between 0 and 23 ? Don't you want a *time* that means the HOUR and MINUTE? If you have a TIMESTAMP datatype column in WOSTATUS.CHANGEDATE then you can use TIMESTAMPDIFF function with first parameter=4 (for minutes) to get duration by subtracting the two timestamp values. – mao Jan 25 '18 at 08:41
  • My code is wrong because I dont know how to get duration minute by subtracting 2 timestamp. Can you give me some example please sir? Is it like this sir :- SELECT TIMESTAMPDIFF((SELECT WOSTATUS.CHANGEDATE FROM WOSTATUS WHERE WOSTATUS.STATUS = 'COMPLETE' AND WONUM='1018' )- (SELECT WOSTATUS.CHANGEDATE FROM WOSTATUS WHERE WOSTATUS.STATUS = 'ACCEPT' AND WONUM='1018'),MINUTE) AS DURATION FROM WOSTATUS ? – kent Jan 25 '18 at 09:50
  • Show the Db2datatype name of WOSTATUS.CHANGEDATE . – mao Jan 25 '18 at 11:50
  • WOSTATUS.CHANGEDATE datatype is timestamp. WOSTATUS.STATUS is varchar. Need to subtract WOSTATUS.STATUS(complete - accept). The id is WONUM from other table which is join with WOSTATUS table. So one id have 2 data timestamp which is complete and accept, need to subtract to get the duration of work in minute. I do not want to access the timestamp field directly but i need to be able to capture the work duration just by referring to wonum. Really Appreciate your help sir. Kind regards. – kent Jan 26 '18 at 01:30
  • Use minutes_between like this:select minutes_between(current timestamp, current timestamp + 1 day) from sysibm.sysdummy1 1 -------------------- -1440 1 record(s) selected. – MichaelTiefenbacher Jan 26 '18 at 06:39