What is mysql Datatype for this format of value..?

One of my csv row value is date and time and is in below format

Timestamp
08-09-2015 06:20
08-09-2015 06:30
08-09-2015 06:40
08-09-2015 06:50
08-09-2015 07:00
08-09-2015 07:10

user is uploading this value to mysql db…
What should be the mysql datatype forthe above mentioned value…?? ive tried with all datatypes…It is reading the value properly but while storing its saving as 0000-00-00 00:00:00.

There is no mysql datatype for this kind of value. It have to be converted into proper format first and then stored with the DATETIME datatype.

$date = "08-09-2015 06:20"'
$newdate = date_create_from_format("d-m-Y H:i", $date)->format("Y-m-d H:i");

you may wish to change the format string d-m-Y to m-d-Y in case the month goes first in your date.

2 Likes

Hi I thought this article was really useful. I just store them as unix time stamps and then format them with php. That actually may not be a possibility for your case scenario but thought I’d share it anyways.

This article is useless for him.

Always worth a try. Php is really good for dealing with dates so no need to store in a date format. As I implied he might not have that call though

Always worth to read the question you are trying to answer, instead of posting a relevant article just in case.

The OP don’t have any date yet for starter. And this is his first and foremost problem to deal with, for which your article offers no help whatsoever.
As of your opinion for storing dates as integers, it is not supported by other developers.

And would you be so kind to point out why that opinion is not supported by other devs?

Nope, I wouldn’t as it would be off topic for this question.
If you have a question of yours, you should ask it in a separate topic instead of hijacking this one.

Uhhmmm…since when is August 9th, 2015 (or September 8th, 2015 depending on his locality) at 6:20am, 6:30am, and 6:40am not valid dates? And why would you need to convert it to a date format before putting it into a database? As long as the field in the database is set to a datetime data type then this works just fine. I’ve NEVER had to convert a text string to a specific date format before inserting it into a database…

INSERT INTO TableName (insertDate) VALUES ('08-09-2015 06:20')
INSERT INTO TableName (insertDate) VALUES ('08-09-2015 06:30')
INSERT INTO TableName (insertDate) VALUES ('08-09-2015 06:40')
1 Like

Looks like your database is more intelligent than mine.

mysql> CREATE TABLE datetest (`dtm` datetime);
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO datetest values ('08-09-2015 06:20');
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'dtm' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM datetest;
+---------------------+
| dtm                 |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.31-log |
+------------+
1 row in set (0.00 sec)

Oops. You’re right. I haven’t done php/mysql work in a while. - I forgot that mySQL isn’t as intelligent in dealing with dates as sql server. :blush:

I disagree, sure the underlying issue remains the same, you need to store the correct value in the database, but the article @Andres_Vaquero links to, gives you a way of then handling said data back to your presentation layer. Does it solve the underlying problem directly? No, but if he read the article and took away that you can store it as a unixtimestamp… well that indirectly gave him a clue that changing the format he uses to insert the data (and possibly the underlying data type of his table) would ultimately solve his issue.

Whether he converts his “pseudo date/time” into a date format that MySQL actually understands or to a unixtimestamp before inserting it into the database doesn’t really matter. That process is identical for both scenarios. Once the data is stored he reads it and converts it into whatever output format he chooses.

2 Likes

Nowhere in this article he can learn how to convert his “pseudo date/time” into a unix timestamp. So the problem remains unsolved.
That’s what exactly I mean saying this article is useless for him.

We’ll agree to disagree on that. I see many opportunities in the article that share how to take a pre-existing “date format” and convert it to a unixtimestamp or convert it into a format that MySQL would be happy with regarding its DateTime data type.

1 Like

You guys are very good with theorizing about some generalized problems.
In general - yes - this article could help someone with some abstract problem about date and time issues.

But for some reason you’re paying little attention to the certain particular question asked.
As a result, not only this link reminds the notorious “RTFM” from the newsgroups’ era, sending the op in the answer’s general direction without any certain clue saying “just read this wall of text!”, but in fact it’s even worse - as no answer can be found at all.

I understand your jealousy toward your home resource. But blindly defending its purity in such a situation is not the best strategy. In your place I would rather edit the article adding a section on format conversions and then linked this particular section in this answer.

Would you rather us just write their code for them? Or guide them into thinking about their problem so they identify it and resolve it?

To each their own in how they view providing help to others. I have no gain in the Sitepoint link. I simply stated, it provides value in that it shows how to take an input that is to represent a date/time and convert it, multiple times, using multiple inputs/outputs. Is that not what the person here needs to do to insert his value into his table? /shrug

1 Like

I could argue against some points of that article. eg. the opening and closing lines.

When working in any programming language, dealing with dates and time is often a trivial and simple task.

.

If there are two main points to take away from the article, they would be to 1) stick with Unix time and 2) stick with UTC as the base time zone for all dates when working with PHP and MySQL.

I can’t speak for others, but when I first started, working with date and time was anything but trivial and simple for me.

I’m biased against epoch time because I can not mentally convert “seconds from” into a recognizable datetime. I much prefer yyyy-mm-dd hh:mm:ss

But to be fair to the author, that article has a main focus on working with time zones which is something I have only rarely needed to work with.

In any case IMHO it is a good idea to not store values in “human friendly” format and to use a format supported by MySQL so native MySQL functions can be used to work with the values.

https://dev.mysql.com/doc/refman/5.7/en/datetime.html

2 Likes

Would you rather us just write their code for them?

Aren’t you guys doing it in the every topic here? :slight_smile: Earnestly and tirelessly?
But OK, I understand a patriotic feeling towards your home resource when a stranger criticizes it, you just can’t help it even if you can’t provide a certain citation to defend your point. So I’d better let it go.

@colshrapnel@cpradio@DaveMaxwell… Each one of u are best programmers…And im student who is trying to develop an application individually and learn… So ive just asked the possibilities to solve the problem i am facing… So that i can resolve the problem and Broaden my tech skills like u guys… Thank u for ur reply… Ill go thru and will implement the best possible solution…

@colshrapnel…Thank u man for giving me an idea…i solved my issue