Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

sqlite stored date as bigint, how to show it as a string to user

  1. User

    sqlite stored date as bigint, how to show it as a string to user

  2. Assistant

    To display a stored date in SQLite as a string to the user, you can use the strftime function.

    Here's an example of how you can achieve this:

    SELECT strftime('%Y-%m-%d', datetime_column, 'unixepoch') AS formatted_date
    FROM your_table;
    

    In the above query datetime_column represents the column name where the date is stored, and your_table represents the table name.

    The strftime function formats the date according to the specified format. In this case, %Y-%m-%d represents the format 'YYYY-MM-DD'. The datetime_column is passed as a second argument to strftime, and the third argument 'unixepoch' is used to convert the stored date from Unix timestamp format to a readable string.

    Replace 'YYYY-MM-DD' with your desired date format, such as 'MM/DD/YYYY' or 'DD/MM/YYYY', according to your preference.

    This query will retrieve the stored date from the column and display it as a string in the specified format for each row in the table.

  3. User

    not work, the date shows something like "-141-03-01"

  4. Assistant

    I apologize for the confusion. It seems like the date stored in the bigint format is not in Unix timestamp format.

    In that case, you need to convert the bigint value to a valid date format. Assuming the bigint value represents Unix timestamp in milliseconds, you can use the following approach:

    SELECT datetime(datetime_column / 1000, 'unixepoch') AS formatted_date
    FROM your_table;
    

    In the above query, datetime_column represents the column name where the bigint value is stored, and your_table represents the table name.

    Here, the datetime function is used to convert the bigint value to a valid date format. We divide the datetime_column value by 1000 to convert the value from milliseconds to seconds as Unix timestamp is typically recorded in seconds. The second argument 'unixepoch' is used to interpret the converted value as a Unix timestamp.

    This query will retrieve the stored bigint value from the column and display it as a string in a formatted date for each row in the table.

  5. User

    still not work. the date is stored by dotnet program through sqlite library

  6. Assistant

    I apologize for the confusion. If the date is stored as a bigint value using .NET and the SQLite library, it is likely using the Unix timestamp format. In that case, you can use the substr and datetime functions to convert the bigint value to a readable date format.

    Try the following SQL query:

    SELECT datetime(datetime_column / 10000000 - 62135596800, 'unixepoch', 'localtime') AS formatted_date
    FROM your_table;
    

    In this query, datetime_column represents the column name where the bigint value is stored, and your_table represents the table name.

    The datetime function is used to convert the bigint value to a readable date format. The datetime_column value is divided by 10,000,000 to convert it to seconds, then subtracted by 62,135,596,800 to adjust it to the Unix timestamp starting point (January 1, 1970).

    The third argument 'localtime' converts the timestamp to the local time zone. If you want to display the date in UTC, you can remove this argument.

    This query should retrieve the stored bigint value from the column and display it as a string in a formatted date for each row in the table.

  7. User

    good!!!!! It works!!!

  8. Assistant

    Great! I'm glad to hear that it works for you. If you have any more questions or need further assistance, feel free to ask.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.