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

asked 10 Jun '20, 14:16

Kris's gravatar image

Kris
34113
accept rate: 0%

edited 10 Jun '20, 14:17

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050

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.

(10 Jun '20, 14:23) Breck Carter

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

(10 Jun '20, 14:36) Kris
Replies hidden

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

(10 Jun '20, 15:22) 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 
permanent link

answered 10 Jun '20, 15:19

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

(10 Jun '20, 17:05) Kris
Replies hidden

@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";

(10 Jun '20, 19:37) Baron
Replies hidden

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

(11 Jun '20, 06:38) Volker Barth
1

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.

(11 Jun '20, 06:43) Volker Barth

OK, got it!

(11 Jun '20, 07:37) Baron

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

(11 Jun '20, 09:08) Breck Carter

@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

(11 Jun '20, 14:50) Kris
showing 3 of 7 show all flat view
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;

permanent link

answered 11 Jun '20, 14:50

Kris's gravatar image

Kris
34113
accept rate: 0%

edited 11 Jun '20, 14:53

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×11

question asked: 10 Jun '20, 14:16

question was seen: 1,536 times

last updated: 11 Jun '20, 14:54