On Thu, 06 Dec 2007 at 2:56 AM, Anonymous wrote

1. Date functions:

sqlite> select current_timestamp;
2007-12-05 15:47:03
sqlite> select current_date;
2007-12-05
sqlite> select current_time;
15:47:08
sqlite> select current_timestamp;
2007-12-05 15:47:11

See http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions

2. MODIFY is not a standard SQL command. You can use ALTER TABLE to a limited degree.

3. LIMIT, for those databases that supported, are not supported for UPDATE and DELETE statements (e.g. Postgres), only MySQL does this.


1: Comment Link

On Thu, 06 Dec 2007 at 8:24 AM, Michael Dale (of michaeldale.com.au) wrote

Thanks for the comment!

1) I'm not sure if these date functions will do what I want. Some examples:
On my archive page I use the following SQL:
SELECT count(post_date) as `count`, YEAR(post_date) as `year`, MONTH(post_date) as `month`, MONTHNAME(post_date) as `month_name` FROM $bt_tb->posts WHERE post_type = 'published' GROUP BY year,month ORDER BY year;

I don't see how this is possible with SQLite.
Another example:
DAYOFMONTH(post_date) = :day

Again I don't know if this is possible.

2) I want to use the MODIFY command to change the column structure. i.e I no longer want "NOT NULL". In MySQL I do the following:
ALTER TABLE $bt_tb->users MODIFY active_key varchar(32)

How would one do this in SQLite?

3) I can live with this.

Again thanks for the comment. Hopefully I can work out how to get this stuff working in SQLite.

2: Comment Link

On Wed, 10 Sep 2008 at 4:13 AM, Jason wrote

Hey Michael, #2 is actually super easy - you just create a temporary table, insert all of your data from your old table to the temporary table, then create a new table with the structure you want, then import the data from your temporary table into your new table, then delete the temporary table. it's so easy, don't you just love SQLite?! http://www.sqlite.org/omitted.html

3: Comment Link