How to store time with PHP and SQL?

PHP
1

I want posts to be sorted by time (I can do that).
But first I need to INSERT it into database.
Do I just use date()? Or is there any other better method like timestamp?
Also, which type of field type do I use there’s date and timestamp.
But are PHP and SQL “date” intercompatible?
(01-01-2000 01:23PM and 01.01.00 13:24+00000000 are not for example)

Do I just upload date() to field type date and everything will be alright?
I need to sort it by time later (including time).
Is this the way of doing it?

2

From my experience, and because of the reasons you’ve already explored, using a timestamp in your database is the best solution. Save the data according to Zulu time (GMT) and then use PHP to manipulate date formatting and timing.

Scott

2 Likes
3

What time is it? Time created or time last edited?

From the manual:

CREATE TABLE t1 (
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

4

Seconded wholeheartedly.

Way back when I used Unix time (the number of elapsed seconds since Jan 1 1970) because it worked well with code.

But I never really cared for it much because seeing something like “437654876” or whatever means absolutely nothing to me. I certainly don’t want to mentally “translate” it into a format I’m more familiar with even if I did know how to.

For me, I’m used to expressing date / time like
“Aug 31, 2016 4:26:52 PM”

So there is some temptation to store date / time in the database just like that. Or maybe like “08-31-2016 4:26:52 PM” etc.

Maybe fine enough if the only use for the field will only ever be to have it fetched and displayed.

HAH! don’t fool yourself. You can almost bet there will come a time when you want to do more with it.
Then you’ll need code to rearrange the date / time into something it can work with and then code to rearrange it back to “human readable” format. And code you will need because such field values can not take advantage of date / time strengths native to MySQL (like sorting, comparisons, intervals)

Now I like timestamp eg. “2016-08-31 16:26:52”
I can easily understand “year month day hour minute second” when I look at “raw” values.
A lot of work can be done by MySQL with properly crafted queries so there is less work for code to do. Usually the only code needed is to “translate” it to “human readable” format.

2 Likes
5

Created. But it shouldn’t quite matter, timestamp it is.

1 Like
7

just add to @Mittineague’s post the advantage with YmdHis is that it always gets bigger. If you did it day month year then 23012016 is bigger than 12022016 so you can’t easily do a greater than query as that would be wrong. But the other way round you can easily do:

<?php if($date >'20160101120000'){echo 'Date is past the 12 on the first of jan';}?>

i think i normally have the field set as ‘datetime’ and i think it doesn’t matter if you insert it as ‘2016-12-10 12:00:00’ or ‘20161210120000’ as it interprets it as a date.

with <?php date('DmY H:i:s', strtotime($row['date']));?>
You can pretty much output it as you like just change the order of the format part.

hth

8

The point I failed to clarify is there is no need to INSERT a TimeStamp or DateField. MySQL can do this automatically on either create or update but not both.

When creating the table set the relevant field to AUTO_UPDATE

http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.