CODEWITHSUNDEEP
c++databasesqlite
danial weaber
danial weaber

Reputation: 876

Storing dates and calculating age in SQLite

In my program I need to save peoples birthday in a sqlite database. Then within the program I need to display the birthday as well as their age too.

Is their a special way to save dates in one column or do I need to save them seperatly day, month and the year.

My other question is how to get their age accurately as years, month and days. is it possible to do this with some kind of function.

Upvotes: 3

Views: 2717

Answers (2)

Joe
Joe

Reputation: 57179

The date can be stored in a single column as text, a real or an int and can be used with the builtin date functions.

Documentation:

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.

An example of selecting the users age from the database would be:

-- '2001-01-01' would be the text column where you store the birthdate 
SELECT (strftime('%Y', 'now') - strftime('%Y', '2001-01-01')) - (strftime('%m-%d', 'now') < strftime('%m-%d', '2001-01-01')) AS 'Years'

[reference]

Upvotes: 4

Sebastian
Sebastian

Reputation: 1889

The sqlite documentation at http://www.sqlite.org/datatype3.html recommends to use ISO8601 strings. SQLite also provides some functions you can use in queries: http://www.sqlite.org/lang_datefunc.html

Upvotes: 1

Related Questions