Handling time field in format '+hhmmsss'

Database
Highlighted
Enthusiast

Handling time field in format '+hhmmsss'

Please help with issue around date and time fields?

 

I have two tables I need to compare records against date and time.  Table one has a date and time field that I have created a timestamp from using CAST(CAST(DATE_FIELD AS TIMESTAMP(0)) + (TIME_FIELD - TIME '00:00:00' HOUR TO SECOND) AS TIMESTAMP (0))

Table 2 has a date field as 'dd/mm/yyy' and time field as '+hhmmsss' ('27/06/2017' '+1728130').

 

Any ideas how I can reformat and concatenate the table 2 date and time so I could subtract t1.timestamp from t2.timestamp with no issues?

 

Any pointers would be great.  Thanks.

 


Accepted Solutions
Senior Apprentice

Re: Handling time field in format '+hhmmsss'

Hi,

 

Couple of possibilities:

 

In table2 you say that you have a "date field as 'dd/mm/yyyy' ". Is this 'date field' a real Teradata DATE column or is it a character column?

 

If it is a character column then:

SELECT t1.*
	,CAST( ((date_field (FORMAT 'yyyy-mm-dd'))||' 00:00:00.0') AS TIMESTAMP(1)) AS ts1_a
	,CAST( SUBSTRING(time_field FROM 2 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 4 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 6 FOR 2)||'.'
	 ||SUBSTRING(time_field FROM 8 FOR 1) AS INTERVAL HOUR TO SECOND(1)) AS is1
  ,ts1_a + is1 AS ts1	 
FROM Table2;

This simply shows how to combine a DATE column and your 'time field' into a timestamp. This can then be compared to the TS column on 'table 1'.

 

If the 'date field' in Table2 is a character column then you can use:

SELECT t1.*
	,CAST( ( (TO_DATE(date_field_str,'dd/mm/yyyy') (FORMAT 'yyyy-mm-dd'))||' 00:00:00.0') AS TIMESTAMP(1)) AS ts1_a
	,CAST( SUBSTRING(time_field FROM 2 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 4 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 6 FOR 2)||'.'
	 ||SUBSTRING(time_field FROM 8 FOR 1) AS INTERVAL HOUR TO SECOND(1)) AS is1
  ,ts1_a + is1 AS ts1	 
FROM t1;

In my sample table, column 'date_field_str' is a character column.

 

Adjust these to include the join to 'table 1'.

 

HTH,

Dave

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
1 REPLY
Senior Apprentice

Re: Handling time field in format '+hhmmsss'

Hi,

 

Couple of possibilities:

 

In table2 you say that you have a "date field as 'dd/mm/yyyy' ". Is this 'date field' a real Teradata DATE column or is it a character column?

 

If it is a character column then:

SELECT t1.*
	,CAST( ((date_field (FORMAT 'yyyy-mm-dd'))||' 00:00:00.0') AS TIMESTAMP(1)) AS ts1_a
	,CAST( SUBSTRING(time_field FROM 2 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 4 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 6 FOR 2)||'.'
	 ||SUBSTRING(time_field FROM 8 FOR 1) AS INTERVAL HOUR TO SECOND(1)) AS is1
  ,ts1_a + is1 AS ts1	 
FROM Table2;

This simply shows how to combine a DATE column and your 'time field' into a timestamp. This can then be compared to the TS column on 'table 1'.

 

If the 'date field' in Table2 is a character column then you can use:

SELECT t1.*
	,CAST( ( (TO_DATE(date_field_str,'dd/mm/yyyy') (FORMAT 'yyyy-mm-dd'))||' 00:00:00.0') AS TIMESTAMP(1)) AS ts1_a
	,CAST( SUBSTRING(time_field FROM 2 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 4 FOR 2)||':'
	 ||SUBSTRING(time_field FROM 6 FOR 2)||'.'
	 ||SUBSTRING(time_field FROM 8 FOR 1) AS INTERVAL HOUR TO SECOND(1)) AS is1
  ,ts1_a + is1 AS ts1	 
FROM t1;

In my sample table, column 'date_field_str' is a character column.

 

Adjust these to include the join to 'table 1'.

 

HTH,

Dave

 

 

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com