Core Data date string converts to wrong day

[patricks]

I have a core data sqlite database with the following timestamp:

Presented in integer:

If I change the settings to present it as core data string:

It looks like the day is wrong, because if I convert it via the website: https://www.epochconverter.com/coredata

I get the following result:

[hankinsoft]

Closed for missing environment details. Please reopen once they have been provided.

**Environment (please complete the following information):**
 - Device: [e.g. iPhone6, MacBook]
 - OS: [e.g. iOS8.1, macOS 11]
 - SQLPro app Version [e.g. SQLPro Studio 2022.x] Please ensure you specify the full app name. (ex: SQLPro Studio, SQLPro for MySQL, etc).
- Target database server [e.g. MySQL, PostgreSQL, Microsoft SQL Server, etc]

**NOTE**
If your environmental details above were not completely filled in, the bug report will be closed until they are provided.

[patricks]

Environment

• Device: MacBook Pro
• OS: macOS 13.2.1 (22D68)
• App: SQLPro for SQLite (Version 2023.15 (Build 4306.5))

[hankinsoft]

Thanks! I've submitted a build to the App Store which I think will fix this. In preferences, there are now two core data options:

1. Core data (local time) string
2. Core data (utc) string

I believe picking the second option should sort this out for you.

Build should be available tomorrow, but I'll pop a note here once I get notified that Apple has approved it.

[patricks]

Thanks for releasing a new version so quickly. I already installed it, but this doesn't fix my problem.

Take a closer look at the day of the ZSTART column. It always displays the 12. as day, but it should be 13. If I use the integer format I get the following output (see screenshot) 700390980. Converting this with https://www.epochconverter.com/coredata the output is correct (see screenshot).

With localtime setting:

With utc setting:

With integers setting:

Converted:

[hankinsoft]

Could you try https://sqlprostudio.s3.us-east-1.amazonaws.com/sqlite/SQLProSQLite.2023.28.app.zip?

I think that should sort it. If so, I'll submit the App Store build.

[patricks]

Yes this looks better now 👍.

Another thing is that the "display timestamps as" feature only works in the data view. If I query data (example: SELECT ZSTART * from ZRECORD) it is always displayed as integer. But I think this another problem.

[hankinsoft]

Great, I'll get the build submitted to the App Store.

Another thing is that the "display timestamps as" feature only works in the data view. If I query data (example: SELECT ZSTART * from ZRECORD) it is always displayed as integer. But I think this another problem.

No plans on changing that. The query view is meant to display what the user queries for, so if you want dates there, you would query have to manually query:

SELECT datetime(yourcolumn + 978307200, 'unixepoch','localtime')

[hankinsoft]

The App Store version (2023.28) is now available. I'm going to close this issue, but if you find it's not working as expected please feel free to re-open.