Substract time

PHP
1

hello forums

Im trying to input a utc time stamp in mysql. I have a date picker and it gives me this format:

2010-06-08 24:27:18 (GMT)

however I want it to be inserted in the databse as a UTC:

2010-06-08 15:27:18 (UTC)

So I figured, I need to substract 9 hours from the gmt to give me the UTC regardless of what the GMT is. (is this even right?)

Question : how do I substract 9 hours from my GMT time with out having to use split() or stuff like that…

Thanks

2

Why do you need to convert it to UTC in the first place ? For most practical purposes, UTC can be considered equivalent to GMT. Why are you subtracting 9 hours ?

3

You will need to do smth like this:


INSERT INTO sometable VALUES ( DATE_SUB('2010-06-08 20:27:18', INTERVAL 9 HOUR) )
4

I don’t know how you tried my code, this code just works and gives the correct value form me:


$date = '2010-06-08 24:27:18';
$ninehours = 3600 * 9;
echo date('Y-m-d H:i:s', strtotime($date) - $ninehours);

Can you show me your code how you tried ?

5

Many thnx golotyuk thats a better solution than doing all the math in php :slight_smile:

6

it gives me :

1969-12-31 15:00:00
I should have
2010-06-08 15:27:18 if I substract 9 hours from 2010-06-08 24:27:18

pretty new to sql but can I use select and insert at thesame time? something like:

INSERT INTO TABLE SET (time_1) VALUES (SELECT(DATE_SUB(‘2010-06-08 20:27:18’, INTERVAL 9HOUR)))

is this even possible?

Thnx

7

Why not use mysql function SUBDATE (DATE_SUB) - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_subdate

SELECT DATE_SUB('2010-06-08 20:27:18', INTERVAL 9 HOUR);
8 

$date = '2010-06-08 24:27:18';
$ninehours = 3600 * 9;
echo date('Y-m-d H:i:s', strtotime($date) - $ninehours);