CODEWITHSUNDEEP
phpmysqlsqlsqlite
Alex
Alex

Reputation: 68492

SQLite does not have a DATE datatype? How can I workaround this?

SQLite doesn't have a data type for dates.

I was wondering if it's enough to make string comparisons between date strings like Y-m-d H:i:s (the standard sql datetime format).

For example ...WHERE date < NOW().... would this fail in certain situations?

Upvotes: 3

Views: 7194

Answers (5)

mu is too short
mu is too short

Reputation: 434665

Using now() won't work because SQLite doesn't know what the means. SQLite does, however, know what current_timestamp means and that has the desired format:

The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

In MySQL, current_timestamp is a synonym for now() and that also has the desired format:

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS'.

So they both use ISO-8601 timestamp formats and just about any database will be able to convert between ISO-8601 strings to and timestamps quite easily so that is a good and portable choice.

Upvotes: 2

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

You can store dates in SQLite with the following data types:

  • 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.

You can then convert these fake dates using the functions listed here.

Now, you also mentioned function NOW() and that won't work in SQLite. That's for MySQL. This will show you SQLite syntax to use:

sqlite> select date('now');
2012-02-12
sqlite> select date('now') = date('2012-02-12');
1
sqlite> select date('now') = date('2012-02-11');
0

So, it is highly recommended for you to use this functions and, on the other side, make sure you don't use NOW().

Upvotes: 5

John Woo
John Woo

Reputation: 263723

Quoted From Roger

SQLite doesn't have dedicated datetime types, but does have a few datetime functions. Follow the string representation formats (actually only formats 1-10) understood by those functions (storing the value as a string) and then you can use them, plus lexicographical comparison on the strings will match datetime comparison (as long as you don't try to compare dates to times or datetimes to times, which doesn't make a whole lot of sense anyway).

Depending on which language you use, you can even get automatic conversion. (Which doesn't apply to comparisons in SQL statements like the example, but will make your life easier.)

Upvotes: 3

Sam Heuck
Sam Heuck

Reputation: 602

Personally I like using unsigned INT and unix timestamps for dates. It is very easy and efficient to compare dates as integers, and PHP has numerous functions for making these dates human readable.

http://php.net/manual/en/function.date.php

Upvotes: 2

John V.
John V.

Reputation: 4670

I'm pretty certain it would fail in certain situations. A faster method would be storing time() values (or mktime()) and generating queries based on time().

This would also work with MySQL.

Upvotes: 1

Related Questions