Air Sqlite Date in YYYY/MM/DD HH:MM Format

0
votes

While I was playing with Air Sqlite, I got some trouble in saving Date in the Database

INSERT INTO tblUserComments (comment_text, comment_cat,comment_date,parent_id) VALUES('"+bubbleText.text+"','"+chosenCat+"',DATETIME('now', 'localtime'),'"+_parentId+"')

store the comment_date in the following format

2455783.2596296296 instead of 2011/08/09 18:13.

How can I save the Date Data as YYYY/MM/DD HH:MM?

Thank you so much.

2
apache-flexsqliteair
What trouble did you have? Is the issue with saving the date;or displaying it for format? Wouldn't you just want to use the database value as the value into a new Date Object and then use a DateFormatter to display it? – JeffryHouser
How is the table structured? Is the comment_date column set as DATE? – J_A_X
@J_A_X, yes it is. comment_date column is set as DATE. – Nazmul
@Flextras.com, For example when I try to do "SELECT SELECT * FROM tblUserComments WHERE comment_date<='2011/08/24'", I do not get appropriate records. – Nazmul

2 Answers

1
votes

After reading up on it, your column can't be set as DATE, since there isn't such a type in SQLite. To quote the dev manual:

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

I would recommend you save the date as an INTEGER of epoc time, then you can do the conversion to whatever either when selecting the rows or within the code. I personally prefer to use the Date class to convert it properly depending on timezone. ie. var date:Date = new Date(epocTime);

1
votes

use parameters instead because it regulates many issues automatically and set the type of your column in sqlite to DATE and assign it a new Date() object directly or assign it a string value in the following format:

private function dateToMySqlFormat(date:Date = null):String
{
    var formattedDate:DateFormatter = new DateFormatter();
    formattedDate.formatString = "YYYY-MM-DD JJ:NN:SS";

    if(date == null) date = new Date;

    return formattedDate.format(date);
}

that should work