0

I am writing a sql query in AWS Glue job script, where I need to covert timestamp to Varchar and extract date from it. Though the syntax seems to work fine if I try in sql but in Glue, it returns the timestamp instead of just the Date even after casting it. Can anybody please tell what is the issue with the query.

Below is my query. Thanks in Advance.


    spark.sql("""SELECT 
                   count(distinct b.user_id) AS count
                 FROM matomo_matomo_log_visit b
                 LEFT JOIN matomo_matomo_log_link_visit_action a ON b.idvisit = a.idvisit
                 WHERE CAST(server_time AS VARCHAR(10)) like '2021-06-%'
                 GROUP BY CAST(server_time AS VARCHAR(10)), b.user_id
               """)
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
ASingh
  • 99
  • 8
  • Which bit is giving you the problem? Is it `CAST(server_time AS VARCHAR(10)) AS serverTime` or `CAST(server_time AS VARCHAR(10)) like '2021-06-%'`? Why not cast it to a DATE first? Or, better yet, compare dates rather than strings. – John Rotenstein Jul 24 '21 at 03:56
  • Hello John, The query runs fine but it's not giving the desired output. `server_time` is a timestamp field and I am trying to CAST TIMESTAMP to VARCHAR datatype and want onlt the date part, that's why VARCHAR(10). Also, I need the dates for just June 2021. But the output I am getting is the whole timestamp instead of the Date(yyyy-mm-dd) part. – ASingh Jul 24 '21 at 04:29
  • Yes, so try casting it to a `DATE` first. – John Rotenstein Jul 24 '21 at 05:27
  • Can you also give an idea if I want the date format as YYYY-MM-DD HH where I have to GROUP BY by the day and hour wise. Eg- 2021-06-01 1, 2021-06-01 2. – ASingh Jul 24 '21 at 05:39
  • @AnshikaTyagi - Regardless of formatting issues, what you're attempting is going to ignore any indices on the tables, making the query run significantly slower. [See my answer here](https://stackoverflow.com/a/24141357/812837). For grouping on the hour, you want something like [the answers here](https://stackoverflow.com/q/14584701/812837). Which duplicate may be relevant? – Clockwork-Muse Jul 24 '21 at 05:43

0 Answers0