SqlHistory.QueryVersions.DateSaved conversion to datetime
Remko1981
Posts: 14 Bronze 1
in SQL Prompt
Does anyone know how to convert the DateSaved value stored in the SQLite database for SqlHistory to a datetime?
Restoring my saved pages became very slow after starting SSMS, which appeared to have happened because of the size of my SqlHistory database. After removing the 300MB SqlHistory.db file from my user directory, SSMS opens the saved pages quickly again. Also, I think the history itself is faster now.
That got me thinking that it would be a good idea to do more aggressive pruning of the history DB so I would not have to throw away all my history every once in a while. Opening the file in a SQLite browser is no problem, but I am stumped as to how the date is saved in the database.
I think the date is stored in SqlHistory.QueryVersions.DateSaved. I looked up a file which, before closing SSMS and SqlHistory, showed me a time of 21:11 and the date was 16-05-2024. The highest number shown for the date in the SQLite SqlHistory.db file was 133594612005169990. Alas, I can find no way to convert this number to the datetime ‘2024-05-16 21:11’. Using Google and Copilot, I could not convert it.
Does anyone know how to convert this? Preferably using SQLite syntax, but SQL Server or Python code would also be a big help!
Restoring my saved pages became very slow after starting SSMS, which appeared to have happened because of the size of my SqlHistory database. After removing the 300MB SqlHistory.db file from my user directory, SSMS opens the saved pages quickly again. Also, I think the history itself is faster now.
That got me thinking that it would be a good idea to do more aggressive pruning of the history DB so I would not have to throw away all my history every once in a while. Opening the file in a SQLite browser is no problem, but I am stumped as to how the date is saved in the database.
I think the date is stored in SqlHistory.QueryVersions.DateSaved. I looked up a file which, before closing SSMS and SqlHistory, showed me a time of 21:11 and the date was 16-05-2024. The highest number shown for the date in the SQLite SqlHistory.db file was 133594612005169990. Alas, I can find no way to convert this number to the datetime ‘2024-05-16 21:11’. Using Google and Copilot, I could not convert it.
Does anyone know how to convert this? Preferably using SQLite syntax, but SQL Server or Python code would also be a big help!
Tagged:
Answers
Hi @Remko1981
Thank you for reaching out on the Redgate forums regarding your question.
I had a look at the SqlHistory.db file and see the column you are referring to. It appears to be some sort of Epoch time but doesn't immediately seem to line up with Unix standard. I can attempt to dig further to see what the actual starting point is, but I do have some python code that may help you.
Example_epoch_ns is where you would enter your SqlHistory.QueryVersions.DateSaved value and it should convert to a current UTC timezone date time value.
Hopefully this is of help with your investigation.
import datetime def custom_epoch_to_datetime(epoch_ns, scaling_factor=0.0778571537083127): # Convert the custom epoch time to seconds time_in_seconds = epoch_ns / (scaling_factor * 1e9) # Convert the seconds to a datetime object in UTC converted_date = datetime.datetime.fromtimestamp(time_in_seconds, tz=datetime.timezone.utc) return converted_date # Example usage example_epoch_ns = 133594612005169990 converted_date = custom_epoch_to_datetime(example_epoch_ns) print(f"Epoch Time: {example_epoch_ns}") print(f"Converted Datetime (UTC): {converted_date}")And of course it's not recommended to directly modify the *.db file... and to always make appropriate backups before manipulating it outside of SQL Prompt.
Hope you can wrangle the code snippet into a usable solution for your needs.