Sending time to mysql as 6:43 p.m. (for example)

[mystic7]

I can't believe this is such a hassle. Google searches bring back results on displaying time differences, calculating the amount of time, etc. but not the syntax for sending the current time in hour:minute am/pm format. Little help, please.

[mystic7]

btw, I found a snippet of code and cut it down to where it only displays the time:

date("g.i a", time());

but the time it shows is 4 hours ahead. Since the server is running off my computer I don't see why it's doing this.

[.josh]

it would be better for you to store a full timestamp or datetimestamp in your database (as a date/time type column) and then format it when retrieving to display it. SQL will be able to handle it better as far as indexing and performing queries.

[Ch0cu3r]

but the time it shows is 4 hours ahead. Since the server is running off my computer I don't see why it's doing this.

Make sure the date.timezone directive is set to your timezone within the php.ini file.

List of supported timezones

Edited October 4, 2013 by Ch0cu3r

[mystic7]

Make sure the date.timezone directive is set to your timezone within the php.ini file.

List of supported timezones

Found it. Changed it. Fixed. Thanks.

Edited October 4, 2013 by mystic7

[mystic7]

it would be better for you to store a full timestamp or datetimestamp in your database (as a date/time type column) and then format it when retrieving to display it. SQL will be able to handle it better as far as indexing and performing queries.

I will consider that in the future. Right now I'm still taking baby steps.

[Barand]

Storing times as 6.30pm in a varchar column instead of 18:30 in a TIME type column is storing up problems for the future. You aren't just taking baby steps you are also putting on shackles.

[mystic7]

Every time I try to do something the "right" way I get code that doesn't work. Further research shows that this syntax or that has been "deprecated".  In ASP If I wanted to show the time it was a simple snippet like time(now). In PHP it's a mini-script in itself. I'll get to the "proper way" of doing things eventually. Right now I'm just trying to make things work and going over the syntax so I can break my ASP shaped way of thinking. 

 

btw, I notice that not one of you really answered my question. You just gave me your opinions on how  I "should" be doing it.

[mac_gyver]

In ASP If I wanted to show the time it was a simple snippet like time(now).

 

 

that's because ASP, in a macro sort of way, hides a lot of the underlying details and removes the choices from the programmer. you do things the ASP way, because it provides the way to do it and there's only that one way.

 

php is a more general programming language, similar to C. you, the programmer, deal with all the details, decisions, and choices yourself. i.e. for a time value - what format is it being input as; is your code validating/processing/formatting it for use internally; is your database column the correct DATA TYPE to store it efficiently and write efficient queries to use the value; does your application have a need to display it in a different format than what it is stored internally in the database...

 

php is definitely a detail orientated, the programmer is in the driver seat, language.

[.josh]

Every time I try to do something the "right" way I get code that doesn't work. Further research shows that this syntax or that has been "deprecated".

This (along with all your other posts) tells me you just go around trying to grab snippets of code and throw it together and hope for the best instead of stepping back to actually learn the language formally. This will inevitably lead to using bad code and things breaking, and it's a bit silly to blame anybody or anything except yourself for this.

 

In ASP If I wanted to show the time it was a simple snippet like time(now). In PHP it's a mini-script in itself.

More confirmation that you aren't really trying to learn the language. If you actually did the research, you would see that ASP and PHP are very similar in regards to date/time handling functions.

 

I'll get to the "proper way" of doing things eventually. Right now I'm just trying to make things work and going over the syntax so I can break my ASP shaped way of thinking.

I get what you're saying, and I don't necessarily disagree with this mentality. Everybody has to start somewhere, baby steps and all that. But it is foolish to summarily dismiss advice given because of this. It's even dumber to get all snippy about it. You came here asking for help, not the other way around. You're acting like a bum walking up to someone's car asking for some money because they are hungry and then bitching because someone gave you food instead of money. Or no enough money, etc.

 

btw, I notice that not one of you really answered my question. You just gave me your opinions on how  I "should" be doing it.

You mentioned breaking your ASP way of thinking. I think perhaps instead you should work on breaking your ASS way of thinking. Again, you came here, asking for help. Free help. You happened to get help from people who do this thing for a living. You will literally not find a person on this entire planet better than Barand when it comes to databases. He's like 100 years old and has been doing database stuff since...you know what, I'm almost positive he had a hand in frakking inventing databases. So when he says something, it's not a matter of opinion, it's a matter of fact. Of course you didn't know this. But just like your coding style, you like jumping out of the airplane without first checking your chute and learning how to use it, or figuring out where you're going to land, and then getting upset at the chute and the ground like they are the ones working against you.

[Barand]

Easy there, .josh. You'll have me believing I should be charging more than five bucks an hour.

[mystic7]

Josh, I would just LOVE to be able to teach you how to play guitar. I wouldn't show you how to play the chords, that would be like stealing snippets of code. No, I would just tell you what the chords are to a song, and if you get it wrong, I won't tell you the right way, I'll just point out that you're doing it wrong. Then you'll know what it's like asking for advice from a stuck up "programmer" like you. Go ahead and delete my account. I'm sure there are more helpful forums out there. To the rest of you, I'm sorry. That you have to deal with a jerk like Josh.

[.josh]

Listen mister,

 

I wasn't being stuck up. I simply told you a better method. YOU initiated the assfuckery by assuming I was just telling you you were doing it wrong. YOU got snippy when it was pointed out that there are better ways of doing things. If you were really interested in knowing those better ways, you would have asked how. But instead, you got snippy about it, so you would have gotten snippy about it regardless of whether or not it was shown to you.

 

Not to mention the fact..I didn't just "point out" you were "wrong". Firstly I didn't even say you were doing it wrong. What you are doing "works." But I tried to mention a better way of doing it, and why. WTF more was I supposed to show you? You already managed to work out making a database and a column, specifying a type and all that. I told you to use a different column type - a date/time based one. WTF more was I supposed to show or tell you about that? Did you want me to walk you through changing the column type? Because it seems based on where you're at now, you already know how to do that.

 

Also, I didn't say piecing together other bits of code was stealing. The "crime" I pointed out was that you don't seem interested in learning what those pieces of code really do, or possible alternatives. And this was all AFTER you started being a douchebag.

 

And you know what? Even if I was and asshat from the getgo - which I wasn't - so the fuck what? AGAIN I will point out to you that YOU came HERE asking for FREE help. So if I decide to be a prick about it, that's my prerogative and you can't rightfully say a damn thing about it. You aren't some paying client or otherwise have some authority to demand or expect people to bend over backwards kissing your ass. Good Lord, I offered a bit of advice to help you out and in no way was I condescending about it and you make out like I'm looking down on you. I wonder how you would have reacted if I really did open things up calling you a fucking idiot. Why do I get the feeling that you're one of those people who resent having to go ask someone for help, assuming they are out to make you feel like an idiot and not actually help, because THAT is what makes those things happen - it's a self-fulfilling prophecy.

[vinny42]

 

 but not the syntax for sending the current time in hour:minute am/pm format.

 

That's because you are trying to do something that nobody else is doing.

In databases it's common practise to use a DATETIME column and to store the current time using NOW();

 

CREATETABLE foo

(id integer, mydate DATETIME);

 

INSERT INTO foo (34, NOW());

 

The DATETIME column is used because you can compare it against the current time, format it to whatever you want, etc.

Storing a date as a string means you cannot do *anything* with that date other than print it, and by the time you get around

to "doing it properly", you'll have a lot of trouble converting all your strings to dates (which is an art in itself).

 

 

 

 

You will literally not find a person on this entire planet better than Barand when it comes to databases.

 

Ouch, that hurts... :-)

[Barand]

I think he meant "on my entire street" rather than "planet"

[vinny42]

 

I think he meant "on my entire street" rather than "planet"

 

[sKunKbad]

Storing times as 6.30pm in a varchar column instead of 18:30 in a TIME type column is storing up problems for the future. You aren't just taking baby steps you are also putting on shackles.

I've read through most of the comments here, and wonder what you think about storing dates as epoch int(10). It seems so much easier to work with than MySql's DATE or DATETIME. I realize there is a difference in the MySQL datatypes, but it seems like more work to use them. In the light of what .josh had to say about you practically inventing the database, I thought I'd ask for your opinion (uh, fact).

[Barand]

If you store the time as you originally proposed (6.43pm) then it is impossible to do comparisons or sorts on the data. 11.00pm will sort before 6.43pm and 7.00am will sort after it. As much use as a chocolate teapot IMHO. Storing times as hh:ii:ss is sortable and is the native format expected by the MySQL DBMS. This also means you can use the many date/time functions without prior conversion to the expected format.

 

Using a unix timstamp (int(10) ) gets around the sortability problem but it still requires conversion before it can be used by the inbuilt functions. It also has the disadvantage that it is not human-readable. There will be times, when debugging for example, when you need to browse the records in a table and readability is a great help.

 

So, my advice is use DATE, DATETIME, TIME or TIMESTAMP type fields (that's what they were invented for) and store your dates and/or times for functionality and not for prettiness - you can format them as you like on output, either in the SQL or in the PHP code..

[vinny42]

 

This also means you can use the many date/time functions without prior conversion to the expected format.

 

You don't have to convert them explicitely, buit the database will do a conversion internally.

 

 It seems so much easier to work with than MySql's DATE or DATETIME

 

In what way do you feel it's easier? Because I usually get this argument from poeple who didn't know that you can do things like:

 

SELECT NOW() + INTERVAL 1 WEEK;

where the '1' can also be a value from a field in a table.

 

and you can do things like 

SELECT date_field - other_date_field;

 

The advantages become more obvious in other databases where you can also store the timezone in the datetime, which means your application can tell the database which timezone each value is in and in which timezone the result should be returned.

This is a lifesaver when you deal with countries that have daylight saving time.

[Barand]

With the exception of unix times (which are just integer seconds values) you cannot just subtract one date value from another, functions are required.

Also conversion is not automatic. Again a function is required.

Example queries below demonstrate this.

CREATE TABLE datetest (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    numdate INT,
    realdate DATETIME,
    otherdate DATETIME
    );
INSERT INTO datetest (numdate, realdate, otherdate)
    VALUES ($now, CURDATE(), CURDATE()+INTERVAL 7 DAY);
    
mysql> SELECT * FROM datetest;
+----+------------+---------------------+---------------------+
| id | numdate    | realdate            | otherdate           |
+----+------------+---------------------+---------------------+
|  1 | 1381142283 | 2013-10-07 00:00:00 | 2013-10-14 00:00:00 |
+----+------------+---------------------+---------------------+


mysql> SELECT numdate + INTERVAL 1 DAY as tomorrow FROM datetest;
+----------+
| tomorrow |
+----------+
| NULL     |
+----------+

mysql> SELECT FROM_UNIXTIME(numdate) + INTERVAL 1 DAY as tomorrow FROM datetest;
+---------------------+
| tomorrow            |
+---------------------+
| 2013-10-08 11:38:03 |
+---------------------+

mysql> SELECT otherdate - realdate as days FROM datetest;
+----------------+
| days           |
+----------------+
| 7000000.000000 |
+----------------+

mysql> SELECT DATEDIFF(otherdate, realdate) as days FROM datetest;
+------+
| days |
+------+
|    7 |
+------+