Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

If I'm creating a date/datetime in a column in Sqlite, do I set it to anything other than 'Text' ?

⇧
0
⇩
57 views

Just getting into SQLite and I understand it does not use datatypes the same way as other languages. I'm building a database and it has to store date and time quite a lot. And I've read a lot about the date and time functions, etc, but I just want to make sure that in my CREATE script I shouldn't have anything other than

BirthDate TEXT
DateTime TEXT
sqlite datetime datatypes create
asked February 20, 2011 9:57 pm CST
Damon
posted via StackOverflow

1 Answer

⇧
1
⇩
Best answer
 

I'm not sure exactly what your question is, but I think the below excerpt may prove to be useful

Taken from http://www.sqlite.org/datatype3.html

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.

answered February 20, 2011 10:23 pm CST
Jason

Your answer

Join with account you already have

Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
Sqlite: update DATE column, but change date only - preserve time
January 11, 2011
Add julian date column to an SQLite table
December 23, 2010
Is there a prefered way to specify a text column in SQLite?
December 20, 2010
Php date() mysql datetime
January 24, 2011
Check only date from datetime field
January 5, 2011
Mysql date and datetime problem
February 11, 2011
MySQL DATETIME - Change only the date
January 14, 2011
Query to convert from datetime to date mysql
January 19, 2011
Store a user-input date string as datetime
January 25, 2011
In sqlite In SQLite How to copy data from one column to another column in database table
January 29, 2011
NHibernate Unable to convert MySQL date/time value to System.DateTime
January 7, 2011
How do i change a readable date and time - such as 01/01/2011 to MySQL UNIX DATETIME (2001-01-01 22:30)
January 1, 2011
What does the Alter Table syntax look like for adding a DATETIME column?
January 26, 2011
Is it possible to add the AUTO_INCREMENT option to a column after creating a table in MySQL 5.1?
February 18, 2011
Select from mysql table between datetime x min ago and datetime x min ago
January 29, 2011
SQLite - Creating encrypted databases. How the...?
January 5, 2011
Android SQLite Creating database
December 24, 2010
Problems with SQLite database, error when creating
December 20, 2010
Creating an index on an SQLite db using DBIx::Class
January 5, 2011
Reading unicode from sqlite and creating a NSString
January 11, 2011
Unknown column 'Date' in 'where clause'
January 19, 2011
MySQL getting each first unique row from sorted by a date column?
February 6, 2011
Error in creating an SQLite database using the Python subprocess module
February 10, 2011
Convert one specific query from text field to date for arithmetic functions
January 25, 2011
How to get a mysql datetime field as a datetime in php?
February 5, 2011
I have a table which contains data with a date column. Mysql Query help needed!
December 29, 2010
Indexing a TEXT column...
February 10, 2011
Max of 4kB for MySQL text column?
January 17, 2011
SQLite Date Query
January 30, 2011
SQLite date sorting problem
January 21, 2011