2

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?

Improve this question
1
  • 1
    Have you considered CONVERT_TZ() mysql function? Commented Jul 8, 2013 at 8:22

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Would you give me an example INSERT statement? In addition what domain should the column storing the date belong to?
INSERT INTO T1(id,date) VALUES (NULL, CONVERT_TZ('2013-07-01 00:04:37','+00:00','-05:00')); I don't undertsand the other part of your question.
Thank you. The other part simply meant "should I store the datetime as a DATETIME value or a TIMESTAMP value?" I understand that TIMESTAMP values are converted to UTC from the system timezone before being stored, and vice versa before being retrieved. How would you correctly store the timestamp in such a situation. I was thinking along the lines of INSERT INTO T1 (id, date) VALUES (NULL, CONVERT_TZ('2013-07-01 00:04:37', '-05:00', (SELECT @@global.time_zone)));.
It's up to you how you save it. I prefer datetime but not always(for example when I need collumns to be updated with CURRENT_TIMESTAMP). If you want to convert from -5:00 timezone to timezone set in SYSTEM then code that you gave is okay.
0

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

Improve this answer

2 Comments

Awesome. How would you store this timestamp into a MySQL database?
$timestamp = date_create('2013-07-01T00:04:37-05:00')->getTimestamp(); And... INSERT INTO table VALUES time = '$timestamp' with your PDO / Mysqli extension... You never do that? :O Check this: link You can set the field as timestamp in mysql ;)
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.