On 20 Mar 2013, at 12:17pm, Dominique Devienne <[email protected]> wrote:
> CURRENT_TIMESTAMP is of text type. Simon's strftime() is also of text type,
> but easily convertible (explicitly via cast, or implicitly via type
> affinity) to an integer. --DD
>
> C:\Users\DDevienne>sqlite3
> SQLite version 3.7.15.2 2013-01-09 11:53:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite>
> sqlite> select CURRENT_TIMESTAMP;
> 2013-03-20 12:12:19
> sqlite> select typeof(CURRENT_TIMESTAMP);
> text
> sqlite> select strftime('%s','now');
> 1363781564
> sqlite> select typeof(strftime('%s','now'));
> text
> sqlite> select cast(strftime('%s','now') as int);
> 1363781631
> sqlite> select typeof(cast(strftime('%s','now') as int));
> integer
Absolutely correct. However, if the result of the strftime() is stored in an
INTEGER column of a table, the affinity of that column makes sure that it is
converted before storage:
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE myTable (a INTEGER, b TEXT);
sqlite> INSERT INTO myTable VALUES (strftime('%s','now'),strftime('%s','now'));
sqlite> SELECT typeof(a),a,typeof(b),b FROM myTable;
integer|1363788461|text|1363788461
so there's no need to do a CAST if you're immediately storing the value. On
the other hand a fussy programmer might prefer to do it anyway.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
