Date add for mysql and sqlite

Question

I have a application with MySQL query where I need add hours to the datetime. For this purpose is easy to use for example

SELECT DATE_ADD(some_column, INTERVAL 4 HOUR) FROM some_table

I'm using Laravel framework and only for testing I'm using SQLite where this date_add function does not exists and I have to use this query

SELECT datetime(some_column, "+4 Hour") FROM some_table

and this is the problem because application is using mysql while PHPUnit is using sqlite. Is there any workaround how to use function to add hours to the datetime which will works for both databases or is there any effective way how to do it?

score 0 · Answer 1 · answered Oct 24 '16 at 12:28

0

Laravel has built in datetime in schembuilder $table->timestamps();

More help https://laravel.com/docs/4.2/schema#adding-columns

answered Oct 24 '16 at 12:28

Marcus

1,850
1
12
24

I know that but I'm using PDO for this purpose. – Jaroslav Klimčík Oct 24 '16 at 12:33

score 0 · Answer 2 · answered Oct 24 '16 at 12:42

0

You can change your date with PHP :

date('Y-m-d H:i:s', strtotime('+4 hours', strtotime($date)))

or

date('Y-m-d H:i:s', strtotime('+4 hours', $date)) if your date is a timestamp

answered Oct 24 '16 at 12:42

EkinOf

451
1
7
18

1

I'm using function date_add as substring in From clause so I need to mysql to filter data and after then I will get the result so I cannot use PHP. – Jaroslav Klimčík Oct 24 '16 at 12:53
Have you check this answer : http://stackoverflow.com/a/32627612/5359470 ? – EkinOf Oct 24 '16 at 12:59
This is only transform from mysql to sqlite but I need function which will work for both databases, not for only one – Jaroslav Klimčík Oct 24 '16 at 13:17

score 0 · Answer 3 · answered Oct 24 '16 at 17:40

0

$date = Carbon:now()->addHours(4);

That will generate a timestamp that is 4 hours ahead of current time

answered Oct 24 '16 at 17:40

Ghost Worker

640
6
17

1

Same as EkinsOf's answer, I cannot use PHP function instead MySQL function to get filtered data in subquery – Jaroslav Klimčík Oct 25 '16 at 06:23
edit your question to show us some sample record of the table and lets see the `DDL` of the table in question – Ghost Worker Oct 25 '16 at 06:50

Date add for mysql and sqlite

3 Answers3