MYSQL date time zone alteration

[dgburton]

Hi there,

I use the following code to grab a timestamp value from my DB and convert it into readable form:

SELECT DATE_FORMAT(lastVisit,\"%T : %d-%m-%Y\") AS lastVisit, ....... etc etc

My question is, seeing as my server is about 5 hours behind my clients who will be viewing the site - is there a quick way of adding a few hours onto the timestamp to bring the reported time into line with the local time zone?

Many thanks,

D.Burton

[Bunkermaster]

if you have MySQL 3.22+ here is the trick

SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "5" HOUR);
there you go... replace date by your datetime value and you're a happy fella 🙂

[dgburton]

Your solutions works perfectly but I need to also use the DATE_FORMAT function to format the date in another way. Is there anyway this can be done?

Many thanks 🙂

[Bunkermaster]

use regexp to split your date and display it diferently

ereg( "([0-9]{4})/([0-9]{1,2})/([0-9]{1,2}) ([0-9]{1,2}):([0-9]{1,2}):([0-9]{1,2})$" , $yourDate, $dateSplit );
echo $dateSplit[ 4 ] .":"
    .$dateSplit[ 5 ] .":"
    .$dateSplit[ 6 ] ." "
    .$dateSplit[ 2 ] ."/"
    .$dateSplit[ 3 ] ."/"
    .$dateSplit[ 1 ] ;

this should work

[dgburton]

or just encompass the DATE_ADD with the DATE_FORMAT function, eg:

SELECT DATE_FORMAT( DATE_ADD(lastVisit, INTERVAL 5 HOUR),'%T : %M %D %Y') FROM users WHERE userID='1'

seems to work perfectly 🙂 - thanks for your help 🙂

[Bunkermaster]

lol was too obvious wanted something more fun 😛

[dgburton]

haha 😃 😃