What can be used to convert a long date into just days so format YYYY-MM-DD HH:MM:SS to just YYYY-MM-DD in Postgresql 12



  • I have a bunch of dates which I am trying to use for archiving. In postgresql 12 I am trying to get a whole bunch of row data into a more simplistic format for processing.

    Currently it looks as follows:

    641860323   2021-05-05 08:37:40.414 +0100
    641860324   2021-05-05 08:37:40.497 +0100
    641860325   2021-05-05 08:37:40.510 +0100
    641860326   2021-05-05 08:37:40.513 +0100
    641860327   2021-05-05 08:37:40.541 +0100
    641860328   2021-05-05 08:37:40.575 +0100
    641860329   2021-05-05 08:37:40.623 +0100
    641860330   2021-05-05 08:37:40.720 +0100
    641860331   2021-05-05 08:37:40.883 +0100
    641860332   2021-05-05 08:37:41.178 +0100
    641860333   2021-05-05 08:37:41.185 +0100
    641860334   2021-05-05 08:37:41.251 +0100
    641860335   2021-05-05 08:37:41.315 +0100
    641860336   2021-05-05 08:37:41.318 +0100
    641860337   2021-05-05 08:37:41.326 +0100
    641860338   2021-05-05 08:37:41.348 +0100
    641860339   2021-05-05 08:37:41.443 +0100
    641860340   2021-05-05 08:37:41.522 +0100
    641860341   2021-05-05 08:37:41.663 +0100
    641860342   2021-05-05 08:37:42.091 +0100
    641860343   2021-05-05 08:37:42.097 +0100
    641860345   2021-05-05 08:37:42.290 +0100
    641860344   2021-05-05 08:37:42.292 +0100
    641860346   2021-05-05 08:37:42.294 +0100
    641860347   2021-05-05 08:37:42.437 +0100
    641860348   2021-05-05 08:37:42.490 +0100
    

    This would go on for each day and am looking to get a format with an end result such as the below which is a count of the amount for each day:

    2021-05-05 |  8216682
    

    What could I use to achieve this? If I try and do a group by as using the below get OOM issues.

    SELECT event_created, COUNT(*)
    FROM tmp_schema.tmp_game_archive
    GROUP BY event_created
    ORDER BY 1
    


  • You can cast the timestamp to a date:

    SELECT event_created::date, COUNT(*)
    FROM tmp_schema.tmp_game_archive
    GROUP BY event_created::date
    ORDER BY 1
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2