Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Black, Michael (IS)]

If speed and storage are a concern then as somebody else notedjulianday is 
the way to go.
Just don't confuse CURRENT_TIMESTAMP with CURRENT_TIME -- you can still extract 
just date from the field if you need it.

sqlite> create table t(id,time);
sqlite> insert into t values(1,julianday(CURRENT_TIMESTAMP));
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t(id,time);
INSERT INTO "t" VALUES(1,2456236.05462963); -- you can see storage mode is 
double
COMMIT;
sqlite> select id,date(time) from t;
1|2012-11-04
sqlite> select id,datetime(time) from t;
1|2012-11-04 13:18:40


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Sunday, November 04, 2012 1:34 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"

Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" 
<michael.bla...@ngc.com>:
>CREATE TABLE t(id,time);
>
INSERT INTO t VALUES(1,CURRENT_DATE);
>
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
>
INSERT INTO t VALUES(3,datetime('now'));
>
INSERT INTO t VALUES(4,date('now'));
>
SELECT * FROM t;
>
1|2012-11-02
>
2|2012-11-02 14:10:15
>
3|2012-11-02 14:10:15
>
4|2012-11-02
>
>
Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...
Indeed, I was never aware of CURRENT_*.

Anyway, all these functions return current moment as _string_ and this is not a 
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add & substract;
etc.




>
>
Michael D. Black
>
Senior Scientist
>
Advanced Analytics Directorate
>
Advanced GEOINT Solutions Operating Unit
>
Northrop Grumman Information Systems
>
>

>
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
>
Sent: Friday, November 02, 2012 8:08 AM
>
To: General Discussion of SQLite Database
>
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
>
>
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:
>
>
>
>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
>
>
>
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
>
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
>
>
>
Please read
>
>
>
>
>
<http://www.sqlite.org/lang_datefunc.html>
>
>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.
>
>
It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.
>
>
Compare:
>
MS SQL: CURRENT_TIMESTAMP
>
PostgreSQL: now()
>
Oracle: sysdate
>
>
To:
>
Sqlite: strftime('%s','now')
>
>
>
>Simon.
>
___
>
sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Roger Andersson]

-Ursprungligt meddelande- 
From: Baruch Burstein

Sent: Sunday, November 04, 2012 10:43 AM
To: Григорий Григоренко ; General Discussion of SQLite Database
Subject: Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.

- Reply -
There might be some that does, but
Oracle11g
SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---
2012-11-04 11:05:23,537000 +01:00

Oracle9i
SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---
2012-11-04 11:08:48,942211 EUROPE/STOCKHOLM

MySQL 5
mysql> select CURRENT_TIMESTAMP;
+-+
| CURRENT_TIMESTAMP   |
+-+
| 2012-11-04 11:06:31 |
+-+

Regards
Roger 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Baruch Burstein]

CURRENT_TIMESTAMP returns "-MM-DD HH:MM:SS", not a unix timestamp. I
think other DB systems all use this function to return a unix timestamp.

On Sun, Nov 4, 2012 at 9:34 AM, Григорий Григоренко <grigore...@mail.ru>wrote:

>
>
>
> Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" <
> michael.bla...@ngc.com>:
> >CREATE TABLE t(id,time);
> >
> INSERT INTO t VALUES(1,CURRENT_DATE);
> >
> INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
> >
> INSERT INTO t VALUES(3,datetime('now'));
> >
> INSERT INTO t VALUES(4,date('now'));
> >
> SELECT * FROM t;
> >
> 1|2012-11-02
> >
> 2|2012-11-02 14:10:15
> >
> 3|2012-11-02 14:10:15
> >
> 4|2012-11-02
> >
> >
> Perhaps the documentation needs to be better?  Apparently you couldn't
> find this info...
> Indeed, I was never aware of CURRENT_*.
>
> Anyway, all these functions return current moment as _string_ and this is
> not a great way to store datetime in db, isn't it?
> - more memory occupied;
> - slower compare;
> - cannot add & substract;
> etc.
>
>
>
>
> >
> >
> Michael D. Black
> >
> Senior Scientist
> >
> Advanced Analytics Directorate
> >
> Advanced GEOINT Solutions Operating Unit
> >
> Northrop Grumman Information Systems
> >
> >
> 
> >
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
> on behalf of Григорий Григоренко [grigore...@mail.ru]
> >
> Sent: Friday, November 02, 2012 8:08 AM
> >
> To: General Discussion of SQLite Database
> >
> Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for
> "strftime('%s','now')"
> >
> >
> Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:
> >
> >
> >
> >
> On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
> >
> >
> >
> >
> >
> > it is a common practice to store datetime values as UNIX time UTC.
> >
> >
> >
> > Maybe, Sqlite should have some shortcut for evaluating current moment?
> >
> >
> >
> >
> >
> Please read
> >
> >
> >
> >
> >
> <http://www.sqlite.org/lang_datefunc.html>
> >
> >
> >
> Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking
> functions that modify or format date values.
> >
> >
> It's about having useful shortcut for getting current moment that doesn't
> have (string) parameters and so can be easily remembered and typed.
> >
> >
> Compare:
> >
> MS SQL: CURRENT_TIMESTAMP
> >
> PostgreSQL: now()
> >
> Oracle: sysdate
> >
> >
> To:
> >
> Sqlite: strftime('%s','now')
> >
> >
> >
> >Simon.
> >
> ___
> >
> sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" 
<michael.bla...@ngc.com>:
>CREATE TABLE t(id,time);
>
INSERT INTO t VALUES(1,CURRENT_DATE);
>
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
>
INSERT INTO t VALUES(3,datetime('now'));
>
INSERT INTO t VALUES(4,date('now'));
>
SELECT * FROM t;
>
1|2012-11-02
>
2|2012-11-02 14:10:15
>
3|2012-11-02 14:10:15
>
4|2012-11-02
>
>
Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...
Indeed, I was never aware of CURRENT_*.

Anyway, all these functions return current moment as _string_ and this is not a 
great way to store datetime in db, isn't it?
- more memory occupied;
- slower compare;
- cannot add & substract;
etc.




>
>
Michael D. Black
>
Senior Scientist
>
Advanced Analytics Directorate
>
Advanced GEOINT Solutions Operating Unit
>
Northrop Grumman Information Systems
>
>

>
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
>
Sent: Friday, November 02, 2012 8:08 AM
>
To: General Discussion of SQLite Database
>
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"
>
>
Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:
>
>
>
>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
>
>
>
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
>
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
>
>
>
Please read
>
>
>
>
>
<http://www.sqlite.org/lang_datefunc.html>
>
>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.
>
>
It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.
>
>
Compare:
>
MS SQL: CURRENT_TIMESTAMP
>
PostgreSQL: now()
>
Oracle: sysdate
>
>
To:
>
Sqlite: strftime('%s','now')
>
>
>
>Simon.
>
___
>
sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 2 Nov 2012 10:25:18 -0400 от Richard Hipp :

>
>
>On Fri, Nov 2, 2012 at 10:18 AM, Simon Davies  
>wrote:
>

datetime() will give current date and time
>>
likewise date(), time() etc
>>
>If I read the original post correctly, I think the OP is requesting a new 
>function, "now()", that returns the number of seconds since 1970.  This would 
>be the same as "CAST(strftime('%s','now') AS INTEGER)", just easier to 
>remember.
Correct!


>
>By coincidence, such a function has already been added to the SQLite 
>implementation inside of Fossil.  See:
>
>http://www.fossil-scm.org/fossil/artifact/b743628236c?ln=669-679
>http://www.fossil-scm.org/fossil/artifact/b743628236c?ln=718
>
>Perhaps the OP can simply copy the code above into his own application?
>
I'm using Delphi with sqlite3.dll, maybe I should introduce my own function.






>
>-- 
>D. Richard Hipp
>d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Fri, 02 Nov 2012 15:32:44 +0100 от Clemens Ladisch :
>Igor Tandetnik wrote:
>
> SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause.
>
>
>
> http://sqlite.org/lang_createtable.html#tablecoldef
>
>
SQLite does in fact accept CURRENT_TIMESTAMP anywhere:
>http://www.sqlite.org/lang_expr.html
>
>
Yes, indeed. Perhaps, this should be 
on http://www.sqlite.org/lang_datefunc.html page.





>
Regards,
>
Clemens
>
___
>
sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Clemens Ladisch]

Igor Tandetnik wrote:
> SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause.
>
> http://sqlite.org/lang_createtable.html#tablecoldef

SQLite does in fact accept CURRENT_TIMESTAMP anywhere:
http://www.sqlite.org/lang_expr.html


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Simon Davies]

On 2 November 2012 13:08, Григорий Григоренко  wrote:
>
> Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :
>
>>
> Please read
>>
>>
> 
>>
> Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
> functions that modify or format date values.
>
> It's about having useful shortcut for getting current moment that doesn't 
> have (string) parameters and so can be easily remembered and typed.
>
> Compare:
> MS SQL: CURRENT_TIMESTAMP
> PostgreSQL: now()
> Oracle: sysdate
>
> To:
> Sqlite: strftime('%s','now')

datetime() will give current date and time
likewise date(), time() etc

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Black, Michael (IS)]

CREATE TABLE t(id,time);
INSERT INTO t VALUES(1,CURRENT_DATE);
INSERT INTO t VALUES(2,CURRENT_TIMESTAMP);
INSERT INTO t VALUES(3,datetime('now'));
INSERT INTO t VALUES(4,date('now'));
SELECT * FROM t;
1|2012-11-02
2|2012-11-02 14:10:15
3|2012-11-02 14:10:15
4|2012-11-02

Perhaps the documentation needs to be better?  Apparently you couldn't find 
this info...

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Григорий Григоренко [grigore...@mail.ru]
Sent: Friday, November 02, 2012 8:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')"

Thu, 1 Nov 2012 19:57:42 + от Simon Slavin <slav...@bigfraud.org>:

>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко <grigore...@mail.ru> wrote:
>
>
> it is a common practice to store datetime values as UNIX time UTC.
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
Please read
>
>
<http://www.sqlite.org/lang_datefunc.html>
>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.

It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.

Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate

To:
Sqlite: strftime('%s','now')


>Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Igor Tandetnik]

Григорий Григоренко  wrote:
> Compare:
> MS SQL: CURRENT_TIMESTAMP

SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause. Does this 
satisfy your requirements?

http://sqlite.org/lang_createtable.html#tablecoldef

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Thu, 1 Nov 2012 19:57:42 + от Simon Slavin :

>
On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:
>
>
> it is a common practice to store datetime values as UNIX time UTC. 
>
> Maybe, Sqlite should have some shortcut for evaluating current moment?
>
>
Please read
>
>

>
Surely, I did) Don't get me wrong - my point is not that Sqlite is lacking 
functions that modify or format date values.  

It's about having useful shortcut for getting current moment that doesn't have 
(string) parameters and so can be easily remembered and typed.

Compare:
MS SQL: CURRENT_TIMESTAMP
PostgreSQL: now()
Oracle: sysdate

To:
Sqlite: strftime('%s','now')


>Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Simon Slavin]

On 1 Nov 2012, at 7:55pm, Григорий Григоренко  wrote:

> it is a common practice to store datetime values as UNIX time UTC. 
> Maybe, Sqlite should have some shortcut for evaluating current moment?

Please read



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

[Григорий Григоренко]

Hello,

it is a common practice to store datetime values as UNIX time UTC. 
Maybe, Sqlite should have some shortcut for evaluating current moment?
Some alias for strftime('%s','now') ? Like, "now" or "unixnow":

  created_at   DEFAULT  (now())     vs     created_at    DEFAULT  
(strftime('%s','now'))

It is shorter and more important - it can be written without consulting docs 
for strfime() , I cannot remember parameters  (


Regards,
Grigory Grigorenko 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users