CODEWITHSUNDEEP
phpmysqldatetimetimezoneunix-timestamp
Aadit M Shah
Aadit M Shah

Reputation: 74214

Saving a datetime string from PHP into MySQL

Alright so I have a datetime string in PHP in the Y-m-d H:i:s format. For exampe:

'2013-07-01 00:04:37'

In addition I have a timezone string like:

'-05:00'

I want to convert these two strings into an integer (unix time) and then store it in a MySQL database. How would you do so?

I know that given a string you can get the unix time as follows in PHP:

date_create('2013-07-01 00:04:37')->getTimestamp();

However how would you adjust it so that it accounts for the correct timezone? Also how do you store a unix timestamp in MySQL from PHP?

Upvotes: 2

Views: 597

Answers (3)

josh
josh

Reputation: 10328

When you convert a time and date to a Unix timestamp, it converts it to an integer that represents the number of seconds since January 1, 1970 00:00 UTC. So if you input your timezone into the function to create the timestamp, it will adjust the number of seconds.

You can then save this value in a MySQL int column, since the timestamp is a signed integer. See How should unix timestamps be stored in int columns? for more information on this. You could do something like

INSERT INTO table (timestamp, content) VALUES ('your timestamp','your content');

to insert it into your table.

Upvotes: 0

Robert
Robert

Reputation: 20286

You can use mysql function CONVERT_TZ()

CONVERT_TZ(dt,from_tz,to_tz)

CONVERT_TZ() converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value.

Example:

SELECT CONVERT_TZ('2013-07-01 00:04:37','+00:00','-05:00');

This outputs 2013-06-30 19:04:37 you can use this function in INSERT statement.

Upvotes: 2

Eleazan
Eleazan

Reputation: 438

You can add the timezone ;)

Something like:

date_create('2013-07-01T00:04:37-05:00')->getTimestamp();

As you can see in docs: https://www.php.net/manual/en/datetime.construct.php

Upvotes: 0

Related Questions