Afternoon,
Once again I am back to ask for some help. I have not found a way to format the unix time stamps in my mysql table to a readable date and time. I have we worked my app and this is the last hurdle I have to figure out. Attached is my code, as well as a screenshot of what my app looks like. Thanks for any help you can throw my way.
import tkinter as tk
from tkinter import ttk
import mysql.connector
# Collects Accounting Table data to display
def fetch_data():
mydb = mysql.connector.connect(
host="xxxxx",
user="xxxxx",
password="xxxxxxx",
database="xxxxxx"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT USERNAME,ACCTSTATUSTYPE,FRAMEDIPADDRESS,TIME_STAMP from ACCOUNTING ORDER BY USERNAME desc")
result = mycursor.fetchall()
for item in tree.get_children():
tree.delete(item)
for row in result:
tree.insert("", tk.END, values=row)
mydb.close()
# Functoin 2 - Clears the Accounting Table
def run_command2():
mydb = mysql.connector.connect(
host="xxxxxxx",
user="xxxxxx",
password="xxxxxx",
auth_plugin='mysql_native_password',
database="xxxxx"
)
mycursor = mydb.cursor()
mycursor.execute("TRUNCATE TABLE ACCOUNTING")
print("Table Cleared")
mycursor.close()
mydb.close()
#
root = tk.Tk()
root.title("LKSD Rad Who's Online")
tree = ttk.Treeview(root, columns=("MacAddress", "Type", "IP Address", "TimeStamp"), show="headings")
tree.heading("MacAddress", text="MacAddress")
tree.heading("Type", text="Type")
tree.heading("IP Address", text="IP Address")
tree.heading("TimeStamp", text="TimeStamp")
tree.pack()
fetch_button = tk.Button(root, text="Read Acct Table", command=fetch_data)
fetch_button.pack()
# Create a clear button
btn = tk.Button(root, text="Clear Table", command=run_command2)
btn.pack(pady=20)
# Create a quit button
quit_button = tk.Button(root, text="Quit", command=root.quit)
quit_button.pack(pady=20)
# Start the main GUI
root.mainloop()
Like I stated above, there are many alternatives. However which way that you opt to take, all of the results are going to be strings. So, the current variable that you’re using to display it in the table (the screenshot that you provided), you can replace it with the alternative.
The reason why it appears that it is “freaking” out is because you are not using apostrophes.
Here is your script:
from datetime import datetime
print(format(datetime.now(), '%m/%d/%y %H:%M:%S'))
It works just fine.
Update:
I modified the script in my previous post so that the date is is easier to read.
Replace the call to time.time() with the timestamp column of your query result. It’s been ages since I did any of this stuff (retired in 2019), but you should also be able to define a converter which will do the trick automagically. Every database adapter is a bit different, but here’s how you do that using the built-in sqlite3 module. Note in particular the adapt_datetime_epoch adapter. I assume the mysql adapter has similar capability.
I figured it out with a change to my Select statement:
mycursor.execute("SELECT USERNAME,ACCTSTATUSTYPE,FRAMEDIPADDRESS,DATE_FORMAT(FROM_UNIXTIME(TIME_STAMP), '%m/%d/%y %h:%i:%S') AS DATE from ACCOUNTING ORDER BY USERNAME desc")
Thank you all for the ideas and suggestions. My co-worker came over and helped me out.
As others already commented, you can use the referenced datetime.fromtimestamp() to convert the Unix time to a datetime object that you can then format as needed. The easiest formatting option is datetime.isoformat(), but you can also use datetime.strftime() to get custom formatting. Example using the Python interpreter:
>>> import time
>>> from datetime import datetime
>>>
>>> ts = time.time() # in your case this would be the time got from the db
>>> dt = datetime.fromtimestamp(ts)
>>> print(dt.isoformat())
2025-01-16T15:04:22.922546
>>> print(dt.strftime('%H:%M %d.%m.%Y'))
15:04 16.01.2025
Or calling the strftime() method on datetime objects is good too, as Pekka showed. There is a cheat sheet of the %H and %M codes here: https://strftime.org/
Also just to note, the timestamps in your database are “Unix epoch times”, which are the number of seconds since January 1, 1970. This is returned by the time.time() function, but the datetime methods can be more useful for getting human-readable timestamps.
