CODEWITHSUNDEEP
mysqldatetime
Shoggoth269
Shoggoth269

Reputation: 167

Inputting Date into Database (phpMyAdmin, SQL)

I'm looking to input a bunch of dates/times into a database. All of them are of the following format:

2015-07-15T05:25:13.292+00:00

I have no idea what the +00:00 aspect of the date means, and nothing seems to have any value other than 00:00. Currently, I have the following SQL statement to create the table, although I'd like to find out the proper way to format the table and the SQL statements for a date.

Current table creation:

CREATE TABLE `bookreservation` (`StartTime` varchar(50) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert example:

INSERT INTO `bookreservation` (`StartTime`) VALUES ('2015-07-15T00:00:00.851+00:00');

What would be appropriate CREATE TABLE -statement and how would that change my INSERT INTO statements?

Upvotes: 2

Views: 158

Answers (2)

vhu
vhu

Reputation: 12798

It looks like +00:00 is the timezone offset. In your case you can use STR_TO_DATE() if you can safely ignore the timezone offset. 

STR_TO_DATE('2015-07-15T05:25:13.292+00:00','%Y-%m-%dT%h:%i:%s.%f+00:00')

You can work this into your INSERT statement:

INSERT INTO `bookreservation` (StartTime) 
 VALUES (STR_TO_DATE('2015-07-15T05:25:13.292+00:00','%Y-%m-%dT%h:%i:%s.%f+00:00'));

This will allow you to use DATETIME type for the column:

CREATE TABLE `bookreservation` (`StartTime` DATETIME DEFAULT NULL)

Upvotes: 1

Shadow
Shadow

Reputation: 34243

+00:00 is the time zone information, telling you how much the given time is ahead or behind of UTC. The first bad news is that standard mysql datetime formats do not include time zone information. The timezone of any datetime value is determined by the session's (mysql session) timezone setting.

If the timezone data is always +00:00, then I would not worry too much about it - unless your server is in a different time zone - I would simply chop of the timezone piece and store the data in a datetime field.

If the timezone data can be different (I would endevour to find out if it can be different, don't base your call on appearances), then I would store the datetime in a datetime field and have a separate field for the timezone. The timezone field can be of character type if you only want to display timezone information. If you want to make calculations based on the timezone data, then I would use a decimal field (difference may not be a whole hour).

Upvotes: 1

Related Questions