SQL GroupBy DateTime wo Millisecond

[former_SQLA_member1694986]

Hi, I have a table with two columns; STATE and CREATE_TMSTMP. The CREATE_TMSTMP is a date&time field which include the millisecond. I want to create a group_by using sql on CREATE_TMSTMP without the millisecond. Here is a small sample of my data.

STATE   CREATED_TMSTMP           

TX  Jun 10, 2020 7:42:54.418 AM
CA  Jun 10, 2020 7:42:54.417 AM
AR  Jun 10, 2020 7:42:54.12 AM 
DE  Jun 10, 2020 7:42:54.103 AM
TX  Jun 10, 2020 7:42:54.093 AM
FL  Jun 10, 2020 7:42:54.09 AM 
CA  Jun 10, 2020 7:42:54.068 AM
HI  Jun 10, 2020 7:42:53.697 AM
CA  Jun 10, 2020 7:42:53.652 AM
TX  Jun 10, 2020 7:42:53.641 AM
DE  Jun 10, 2020 7:42:53.636 AM
AR  Jun 10, 2020 7:42:53.599 AM
FL  Jun 10, 2020 7:42:53.47 AM 
CA  Jun 10, 2020 7:42:53.468 AM
TX  Jun 10, 2020 7:42:53.444 AM
MT  Jun 10, 2020 7:42:53.352 AM
CA  Jun 10, 2020 7:42:53.323 AM
The sql might be look like this,
Select
   STATE
   ,Count(STATE) as "Count"
From
   MyTable
Group by
   CREATED_TMSTMP
   ,STATE
Order by
   CREATED_TMSTMP desc
   ,STATE

Expectation: Display two groups (Jun 10, 2020 7:42:54, Jun 10, 2020 7:42:53) with the count on each state. If the CREATED_TMSTMP can be displayed without the millisecond, that would be great. Thank you and much appreciate for your helps and supports.

Best regards, Chris

[Breck_Carter]

I assume you mean "without the fractional second" portion rather than "without the millisecond" which could be misinterpreted as "with only two decimals to the right of the decimal point".

I assume you want the timestamp truncated to the seconds portion, rather than rounded up or down to the nearest second.

A quick and dirty approach would be to use the DATEFORMAT function convert the timestamp to a string without the fractional seconds portion, and use that function call in the SELECT, GROUP BY and ORDER BY clauses.

[former_SQLA_member1694986]

Yes, I want to truncate the timestamp upto the seconds portion.

If it is converted to a string, the sorting become a problem when the data set are across months. For example: May, June, July and August. Please advise.

Thanks. Chris

[Breck_Carter]

To continue the quick and dirty approach, convert the truncated string back to a TIMESTAMP for the purposes of correct ORDER BY.

[Breck_Carter]

CREATE TABLE MyTable ( 
   STATE VARCHAR ( 2 ),  
   CREATED_TMSTMP TIMESTAMP );
INSERT MyTable VALUES ( 'TX', 'Dec 10, 2020 7:42:54.418 AM' );    
INSERT MyTable VALUES ( 'CA', 'Dec 10, 2020 7:42:54.417 AM' ); 
INSERT MyTable VALUES ( 'AR', 'Dec 10, 2020 7:42:54.12 AM' ); 
INSERT MyTable VALUES ( 'DE', 'Dec 10, 2020 7:42:54.103 AM' );
INSERT MyTable VALUES ( 'TX', 'Dec 10, 2020 7:42:54.093 AM' );     
INSERT MyTable VALUES ( 'FL', 'Dec 10, 2020 7:42:54.09 AM' ); 
INSERT MyTable VALUES ( 'CA', 'Dec 10, 2020 7:42:54.068 AM' );

INSERT MyTable VALUES ( 'HI', 'Jun 10, 2020 7:42:53.697 AM' );
INSERT MyTable VALUES ( 'CA', 'Jun 10, 2020 7:42:53.652 AM' );  
INSERT MyTable VALUES ( 'TX', 'Jun 10, 2020 7:42:53.641 AM' );     
INSERT MyTable VALUES ( 'DE', 'Jun 10, 2020 7:42:53.636 AM' );
INSERT MyTable VALUES ( 'AR', 'Jun 10, 2020 7:42:53.599 AM' );
INSERT MyTable VALUES ( 'FL', 'Jun 10, 2020 7:42:53.47 AM' ); 
INSERT MyTable VALUES ( 'CA', 'Jun 10, 2020 7:42:53.468 AM' );  
INSERT MyTable VALUES ( 'TX', 'Jun 10, 2020 7:42:53.444 AM' );     
INSERT MyTable VALUES ( 'MT', 'Jun 10, 2020 7:42:53.352 AM' );
INSERT MyTable VALUES ( 'CA', 'Jun 10, 2020 7:42:53.323 AM' );  
COMMIT;

SELECT DATEFORMAT ( CREATED_TMSTMP, 'Mmm dd, yyyy h:mm:ss AA' ) AS CREATED_TMSTMP,
       STATE,
       COUNT ( STATE )
  FROM ( SELECT STATE, 
                CAST ( DATEFORMAT ( CREATED_TMSTMP, 'Mmm dd, yyyy h:mm:ss AA' ) 
                       AS TIMESTAMP ) AS CREATED_TMSTMP 
           FROM MyTable
       ) AS MyTable
 GROUP BY CREATED_TMSTMP,
       STATE
 ORDER BY CREATED_TMSTMP DESC,
       STATE;

CREATED_TMSTMP          STATE COUNT( MyTable.STATE) 
----------------------- ----- --------------------- 
Jun 10, 2020 7:42:53 AM AR                        1 
Jun 10, 2020 7:42:53 AM CA                        3 
Jun 10, 2020 7:42:53 AM DE                        1 
Jun 10, 2020 7:42:53 AM FL                        1 
Jun 10, 2020 7:42:53 AM HI                        1 
Jun 10, 2020 7:42:53 AM MT                        1 
Jun 10, 2020 7:42:53 AM TX                        2 
Dec 10, 2020 7:42:54 AM AR                        1 
Dec 10, 2020 7:42:54 AM CA                        2 
Dec 10, 2020 7:42:54 AM DE                        1 
Dec 10, 2020 7:42:54 AM FL                        1 
Dec 10, 2020 7:42:54 AM TX                        2 

[former_SQLA_member1694986]

Got error, SAP DBTech JDBC: [328]: invalid name of function or procedure: DATEFORMAT: line 5 col 24 (at pos 171)

I checked SAP HANA's Help and don't find DATEFORMAT function. I'm using SAP HANA Studio v2.3.27. Please advise.

Chris.

[Baron]

@Breck Carter, what is the drawback with this alternative (simple) select statement?

select DATEFORMAT ( CREATED_TMSTMP, 'Mmm dd, yyyy h:mm:ss AA' )

CREATED_TMSTMP1, "State", count(*)

from MyTable group by CREATED_TMSTMP1, "State"

order by CREATED_TMSTMP1 desc, "State";

[VolkerBarth]

We are not dealing with SAP HANA here, but with SAP SQL Anywhere, a completely different database product...

[VolkerBarth]

Well, do you want dates ordered chronologically or as strings in the according date format lexicographically (so April is before January)? In your sample, the order will be lexicographical.

In contrast, Breck's code sorts chronologically.

[Baron]

OK, got it!

[Breck_Carter]

The HANA TO_VARCHAR function might work, but the HANA Help for TO_DATE is really sparse.

[former_SQLA_member1694986]

@Breck Carter, Your sql logic works perfectly. Since I don't have DateFormat function in my SAP HANA Studio, I used your template and combined it with the string conversion for Grouping, and then use the timestamp conversion to display it.

Here is what my code looked like,

SELECT TO_TIMESTAMP(CREATED_TMSTMP, 'YYYY-MM-DD HH:MI:SS') AS CREATED_TMSTMP,
       STATE,
       COUNT ( STATE )
FROM ( SELECT STATE,
          TO_VARCHAR(CREATED_TMSTMP, 'YYYY-MM-DD HH24:MI:SS') AS CREATED_TMSTMP 
         FROM MyTable
     ) AS MyTable
 GROUP BY CREATED_TMSTMP,
       STATE
 ORDER BY CREATED_TMSTMP DESC,
       STATE;

The key is where I used 'YYYY-MM-DD HH24:MI:SS' on the string conversion, instead of 'Mon DD, YYYY HH:MM:SS AM'.

Thank you for your helps and supports, and I have learned a lot from you. Much appreciated it.

Best regards, Chris

[former_SQLA_member1694986]

SELECT DATEFORMAT ( CREATED_TMSTMP, 'Mmm dd, yyyy h:mm:ss AA' ) AS CREATED_TMSTMP,
       STATE,
       COUNT ( STATE )
  FROM ( SELECT STATE, 
                CAST ( DATEFORMAT ( CREATED_TMSTMP, 'Mmm dd, yyyy h:mm:ss AA' ) 
                       AS TIMESTAMP ) AS CREATED_TMSTMP 
           FROM MyTable
       ) AS MyTable
 GROUP BY CREATED_TMSTMP,
       STATE
 ORDER BY CREATED_TMSTMP DESC,
       STATE;

If you don't have DATEFORMAT function, you might want to try this,
SELECT TO_TIMESTAMP(CREATED_TMSTMP, 'YYYY-MM-DD HH:MI:SS') AS CREATED_TMSTMP,
       STATE,
       COUNT ( STATE )
FROM ( SELECT STATE,
          TO_VARCHAR(CREATED_TMSTMP, 'YYYY-MM-DD HH24:MI:SS') AS CREATED_TMSTMP 
         FROM MyTable
     ) AS MyTable
 GROUP BY CREATED_TMSTMP,
       STATE
 ORDER BY CREATED_TMSTMP DESC,
       STATE;