CodeIgniter Forums

Full Version: ActiveRecord & inserting dates
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]Macros[/eluser]
I'm just really digging into CI's database library & the Active Record implementation. I'm running into a problem trying to insert dates into a MySQL database.

My code is:
Code:
$data = array(
    'firstname' => $this->validation->firstname,
    'lastname' => $this->validation->lastname,
    'email' => $this->validation->email,
    'password' => $this->validation->password,
    'created' => time(),
);
$this->db->insert('users', $data);
This causes the date to be invalid and not be inserted correctly. Now, I could just pass thru a string with the date in it, but that's not valid SQL. Would work with MySQL (& I'll probably do that as a fix) but may not if using another server.

This must be a common issue, I don't see any helpers or anything that would help with this, and a search of the forums only found formatting output, not inserts!

Cheers, Cameron.

El Forum

[eluser]AgentPhoenix[/eluser]
I'm not sure how you're wanting to store dates in your database. Personally, I like to use UNIX timestamps because it's a lot easier to format and you can do some timezone support if you want too. I'd definitely check out the Date Helper. From there, you can pass UNIX timestamps to other functions to get MySQL time or even human readable time. Then, like you have with your time() function, just put now() or something and it'll work. That's what I've been doing lately in my own application. Smile

El Forum

[eluser]Macros[/eluser]
Ah, scratch this thread. I think I was getting caught up in details.

The SQL timestamp format is: {ts'2008-07-02 00:05:00'} which is an SQL date type;

but, '2008-07-02 00:05:00' (type: string) seems to also be accepted by most/all databases.

El Forum

[eluser]Yash[/eluser]
set created field as datetime type

now use this code
Code:
date('Y-m-d H:i:s')
for inserting date. It will give you current date and time.

El Forum

[eluser]Doosje[/eluser]
I use these kinda things:
Code:
$this->db->where('datum >= now()');

El Forum

[eluser]FuturShoc[/eluser]
for inserting date. It will give you current date and time.[/quote]

Awesome! Thanks!

El Forum

[eluser]internut[/eluser]
[quote author="AgentPhoenix" date="1214930561"]I'm not sure how you're wanting to store dates in your database. Personally, I like to use UNIX timestamps because it's a lot easier to format and you can do some timezone support if you want too. I'd definitely check out the Date Helper. From there, you can pass UNIX timestamps to other functions to get MySQL time or even human readable time. Then, like you have with your time() function, just put now() or something and it'll work. That's what I've been doing lately in my own application. Smile[/quote]

Interesting topic that I'm trying to figure out the best way to go about things. I usually use a datetime & timestamp field though it seems easier & more powerful to use unix timestamp..

Thoughts?