How can we help you today? How can we help you today?

SqlHistory.QueryVersions.DateSaved conversion to datetime

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!
Remko1981
0

Comments

4 comments

Sort by
  • Jon_Kirkwood

    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.

    Jon_Kirkwood
    0
  • Remko1981
    Super! I am going to test this out. Thanks for the help!
    Remko1981
    0
  • Jon_Kirkwood
    Excellent, glad to be of assistance.
    Hope you can wrangle the code snippet into a usable solution for your needs.
    Jon_Kirkwood
    0
  • SQLHatGuy
    It looks like SQLPrompt is using LDAP/AD/NT timestamp format which is the number of 100ms ticks since 1/1/1601.  Based on this I came up with this SQL which is working for me so far (more testing needed):

    SELECT
        datetime('1601-01-01', (qv.DateSaved / 10000000)||' seconds')
        ,*
    FROM 
        QueryVersions qv

    SQLHatGuy
    0

Add comment

Please sign in to leave a comment.