CODEWITHSUNDEEP
sqlpython-3.xsqlite
bibscy
bibscy

Reputation: 2708

How to sort a dictionary by date keys

How can I sort the dates ascedingly based both on month and on year?
sorted() will order only by month.

result = cur.execute("""SELECT order_delivered_customer_date
                        FROM orders 
                        WHERE order_status == 'delivered'
                        ORDER BY orders.order_delivered_customer_date ASC""")

# iterate through the query result
#store the month and count of days in month for transaction in dictDates
for row in result: 
    datetp = parser.parse(row[0])  #convert the string to date type  
    
    parsedDate = datetp.strftime('%m-%Y') #format to month and year

    if parsedDate in dictDates:
        
        existingVal = dictDates[parsedDate]
        updateVal = existingVal + 1
        dictDates[parsedDate] = updateVal
     
    else:
        dictDates[parsedDate] = 1
print(dictDates)

##Output {'12-2017': 23801, '01-2017': 7569, '01-2018': 24498, '11-2018': 8983, '06-2018': 27654, '07-2018': 24082, '03-2018': 29526, '10-2018': 9315, '05-2017': 17254, '08-2018': 31551, '11-2017': 23509, '05-2018': 27297, '12-2018': 7009, '04-2018': 28024, '09-2018': 10455, '10-2017': 18852, '02-2017': 8977, '07-2017': 16889, '09-2017': 17492, '04-2017': 10982, '08-2017': 17791, '03-2017': 13630, '02-2018': 26582, '06-2017': 15661, '10-2016': 771, '06-2016': 93, '04-2016': 33, '07-2016': 78, '05-2016': 11, '08-2016': 39, '09-2016': 24, '11-2016': 111, '12-2016': 20, '01-2016': 32, '03-2016': 20}

Upvotes: 0

Views: 82

Answers (2)

forpas
forpas

Reputation: 164174

The format of your dates is not comparable.
You must extract the year and the month with a string function like SUBSTR() and sort by them:

SELECT order_delivered_customer_date
FROM orders 
WHERE order_status = 'delivered'
ORDER BY SUBSTR(order_delivered_customer_date, 4), 
         SUBSTR(order_delivered_customer_date, 1, 2)

Upvotes: 1

andrewalenta
andrewalenta

Reputation: 81

depending on the dbms this should work:

result = cur.execute("""SELECT order_delivered_customer_date
                    FROM orders 
                    WHERE order_status == 'delivered'
                    ORDER BY year(orders.order_delivered_customer_date),    month(orders.order_delivered_customer_date) ASC""")

Upvotes: 1

Related Questions