datetime -> epoch

[robertaccettura]

I have datetime in a mysqldb in the format of:

2003-12-01 20:27:01

want to go to epoch (as if I ran the time() function on the date specified).

Is there a function or method to do this?

[weekender]

a mysql function

$sql = "SELECT UNIX_TIMESTAMP(datetimefield) FROM tablename";

mysql timestamps are formatted dates, php timestamps are unix timestamps. That one got me for hours when i first had the problem!

[robertaccettura]

I don't want to make a mySQL call each time. Admin will kill me . 😉

Is there a method in PHP?

It's already out of the DB, and the DB may be closed.

[weekender]

umm you don't have to do a mysql call each time - just do it at the point where you take the data out of the database in the first instance... you said the data was in a mysql db?

there is a way of doing it in php, i'll explain it if you really want...

[robertaccettura]

Well, I use the other (datetime) format in many places.

So converting it for this one use would be the best approach.

[weekender]

you don't have to convert - the mysql UNIX_TIMESTAMP function takes a mysql field and outputs it as a unix timestamp - the data in the field isn't changed, converted or anything

it's just like saying

$data = "this";
echo "$data and this";

$data is still "this"...

[robertaccettura]

I need to have the datetime in epoch for one particular job.

Otherwise, I always use datetime, and it's stored in datetime.

Rather than store both in the database (rather wasteful), I would perfer to just convert datetime to epoch for that one particular use.

[weekender]

thats what it does - converts it on the fly - the data in the database doesn't change. remember - select statements show the data and don't change it

i think it's best that you have a play and test it for yourself create a dummy copy of your table and run some SELECT statements like above

[robertaccettura]

But this is done much later, well after the data is pulled from the DB.

Hence a solution not using the DB is what's required.

[dnszero]

Just use the php function strtotime().

Weekender is correct, if you are doing a query it is best to extract the data in the format you will need it. Of course, if you need to use the same results in multiple formats, just do use which ever is easiest.

[robertaccettura]

Originally posted by dnszero
Just use the php function strtotime().

Weekender is correct, if you are doing a query it is best to extract the data in the format you will need it. Of course, if you need to use the same results in multiple formats, just do use which ever is easiest.

I agree.

strtotime() is perfect.

Thanks.