CODEWITHSUNDEEP
sql
AdamG
AdamG

Reputation: 3718

Modify SQL Row to set back date

I am currently running a PHP script that is grabbing data from Facebook and putting it into my SQL database. I want to now be able to see what Facebook posts were posted within 2 hours of the data grab, and I realized that the NOW() command was making a time that was 4 hours ahead of the offset for the Facebook server.

This brings me to three questions:

1) Is there a way to do a select command where I can offset the date by 4 hours?

2) Is there a way to modify rows to offset the date by 4 hours?

3) Will these methods actually set the entire date back, so that if a date is, let's say at 1:00 today (March 13th), it will be modified to be 21:00 the day before (March 12th).

Thanks so much!

Upvotes: 1

Views: 509

Answers (2)

Hogan
Hogan

Reputation: 70529

Any place you use a date in SQL you can use the DATEADD function, in a select, in an insert and in a where

 DATEADD(hour,4,datevalue)

To go back just use a negative number

 DATEADD(hour,-4,datevalue)

So, this

DECLARE @datetime datetime = '2013-03-13 01:01:01.110';
SELECT DATEADD(hour, -4, @datetime);

returns this:

2013-03-12 21:01:01.110

Upvotes: 1

Kermit
Kermit

Reputation: 34063

1) Is there a way to do a select command where I can offset the date by 4 hours?

Yes, using DATEADD/DATE_ADD

2) Is there a way to modify rows to offset the date by 4 hours?

You shouldn't be modifying the data.

3) Will these methods actually set the entire date back, so that if a date is, let's say at 1:00 today (March 13th), it will be modified to be 21:00 the day before (March 12th).

If you're using the DATEADD/DATE_ADD function and your date is in a date type, then yes.

SQL Server Manual

MySQL Manual

Upvotes: 1

Related Questions