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 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 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 answered 10 Jun '20, 15:19 Breck Carter 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?
(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
|
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; answered 11 Jun '20, 14:50 Kris 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.
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
To continue the quick and dirty approach, convert the truncated string back to a TIMESTAMP for the purposes of correct ORDER BY.