How to create a SQLite Timestamp value in PHP

Question

I want to generate a timestamp in PHP and then store that value in SQLite.

Here is the code:

$created_date = date("YYYY-MM-DD HH:MM:SS",time());

Here is what gets stored in the DB (which looks wrong): 11/21/0020 12:39:49 PM

How should I change my code to store the current date/time properly in SQLite

Solution 1

Why not just update your SQLite query to use date('now')? reference

But, within PHP you should be able to use

$created_date = date('Y-m-d H:i:s');

Solution 2

I just implemented this for my own project..

My solution was to use the UTC time format officially specified for the web: RFC3339. You can use any of the following PHP time format constants:

• DATE_RFC3339
• DATE_W3C
• DATE_ATOM

Example:

$sqlite_timestamp = date(DATE_RFC3339);
echo "My SQLite timestamp = ".$sqlite_timestamp;

The best part is the alphanumeric order corresponds to the date order, so you can use ORDER BY SQL statements on the date fields!

Comments

[Lucio M. Tato]

previously: date_default_timezone_set("UTC"); because SQLite stores dates w/o timezone delta (also "Z" or +00, or the UTC zone). Wich is the right approach.