SQLite date field

Jonathan

Not sure if this is really a Qt question or SQLite question.

Is there any cunning way of identifying a field as a date field as opposed to an integer field?

stukdev

What you mean?
You want know if a filed is a date or a integer field?

vsorokin

Three ways for date storing has in SQLite.

Quote from "this page":http://www.sqlite.org/datatype3.html:

bq.
1.2 Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

And on "this page":http://www.sqlite.org/lang_datefunc.html described methods for work with date and time.

Jonathan

I've created a general purpose programme for creating, managing, and editing arbitrary SQLite databases.

I'm aware that dates do not have their own storage format. However this makes it hard to decide whether or not a field is a date field. Not knowing makes it difficult to present the correctly formatted data to the user.

stukdev

I'm not sure, but sqlite don't have a struct type of the field at the top of the file of db?

[quote author="Jonathan" date="1292314669"]I've created a general purpose programme for creating, managing, and editing arbitrary SQLite databases.

I'm aware that dates do not have their own storage format. However this makes it hard to decide whether or not a field is a date field. Not knowing makes it difficult to present the correctly formatted data to the user.

[/quote]

[quote author="Jonathan" date="1292314669"]I've created a general purpose programme for creating, managing, and editing arbitrary SQLite databases.

I'm aware that dates do not have their own storage format. However this makes it hard to decide whether or not a field is a date field. Not knowing makes it difficult to present the correctly formatted data to the user.

[/quote]

dangelog

[quote author="Jonathan" date="1292314669"]I've created a general purpose programme for creating, managing, and editing arbitrary SQLite databases.

I'm aware that dates do not have their own storage format. However this makes it hard to decide whether or not a field is a date field. Not knowing makes it difficult to present the correctly formatted data to the user.

[/quote]

You can't; you can try a best guess, i.e. the column has type TEXT and all the rows are formatted like ISO8601 strings then it's very likely that it contains dates (of course, it is perfectly possible that it simply stores text which "looks like" dates). You can't say anything for INTEGER / REAL types.

My advice would be simply letting the user choose if a given column is storing dates or numbers/text. :-)