473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

arithmetics with timestamp?

Hi everubody,

I have a timestamp field in a mysql DB and I want to know how much time
has past since that timestamp.

is it possible?
Say that I have this value in a variable -
1) how do I get the current system timestamp?
2) how do I subtract the first from the second?

Thanks!

Jan 15 '06 #1
8 1811
Super Mango wrote:
Say that I have this value in a variable -
1) how do I get the current system timestamp?
2) how do I subtract the first from the second?


If you are only interested in the amount of hours, you could do something
like:

$now = time();
$past = time() - 86400;

print 'The difference between $past and $now = ';
echo ($now - $past) / 3600, ' hours';

Of course, you can narrow this figure down to days (div 86400), hours (div
3600), minutes (div 60) and seconds.

These kind of calculations can also be performed by MySQL. Have a look at
the manual @ mysql.com for more info.
JW
Jan 15 '06 #2
thanks!

But the thing is that the value in the DB is kept as YYYY-MM-DD hh:mm
how do I convert that to the "time()" format?

Jan 15 '06 #3
Super Mango wrote:
Hi everubody,

I have a timestamp field in a mysql DB and I want to know how much time
has past since that timestamp.

is it possible?
Say that I have this value in a variable -
1) how do I get the current system timestamp?
2) how do I subtract the first from the second?

Thanks!

I can show you how to do it at a website down the wire using a bash
script. Should be adaptable to something local and PHP.

TIMESTAMP=$(date -d "`lynx -error_file=lynx_error -dump
-connect_timeout=20 $url/timestamp.txt`" +%s)
Jan 15 '06 #4
Super Mango wrote:
thanks!

But the thing is that the value in the DB is kept as YYYY-MM-DD hh:mm
how do I convert that to the "time()" format?


Per example, with strtotime: http://www.php.net/strtotime

JW
Jan 15 '06 #5
>I have a timestamp field in a mysql DB and I want to know how much time
has past since that timestamp.

is it possible?
Yes. Use MySQL to do it.
Say that I have this value in a variable -
1) how do I get the current system timestamp?
now()
2) how do I subtract the first from the second?


timestampdiff(). (requires MySQL 5.0 or greater).
timediff(), but it returns the value in hours, minutes, and seconds.
datediff(), but only the date parts are used.

Gordon L. Burditt
Jan 15 '06 #6
Super Mango wrote:
But the thing is that the value in the DB is kept as YYYY-MM-DD hh:mm
how do I convert that to the "time()" format?


select UNIX_TIMESTAMP(column) from table;

--
Mail to my "From:" address is readable by all at http://www.dodgeit.com/
== ** ## !! ------------------------------------------------ !! ## ** ==
TEXT-ONLY mail to the whole "Reply-To:" address ("My Name" <my@address>)
may bypass my spam filter. If it does, I may reply from another address!
Jan 15 '06 #7
Super Mango wrote:
I have a timestamp field in a mysql DB and I want to know how much time
has past since that timestamp.


<?php
// Assume timestamp from database is in $ts.
$then = strtotime($ts);
$now = time();
$diff = $now - $then;
// $diff is the difference in seconds.
?>

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact

Jan 16 '06 #8
d
"Super Mango" <li********@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
Hi everubody,

I have a timestamp field in a mysql DB and I want to know how much time
has past since that timestamp.

is it possible?
Say that I have this value in a variable -
1) how do I get the current system timestamp?
2) how do I subtract the first from the second?

Thanks!


If you're going to be doing the vast majority of your time maths outside
MySQL, you might want to use integers instead of timestamps - you can just
save the unix timestamp in the database, which saves time converting between
MySQL and unix times. And, of course, you can still search/sort using the
unix timestamp...
Jan 16 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: perplexed | last post by:
How do you convert a user inputted date to a unix timestamp before insterting it into your database? I have a form, with a textfield for a date that the user inputs in the format mm-dd-yyyy and...
6
by: Terence | last post by:
I need some clarification with pointer arithmetics on void *. Example 1: ======== char s; char *ptr = s; ptr += 1; // I assume ptr is increased by 1 byte, pointing to the 2nd element in the...
21
by: Milan Èermák | last post by:
Hi all, I'm about to write an arithmetics object library. It should contain classes like ShortInteger (for 32-bit longs), Double and Float as standard numeric type wrappers and LongInteger (really...
1
by: Jonas Ernst | last post by:
Hi, Can somebody give me some hints how to do a line interpolation without using floating point arithemtics? The function shall do a linear interpolation between 2 points (line interp?) and...
1
by: Rein Anders Apeland | last post by:
Hi all, We are developing a PoS application for Linux and are in need of a LGPL'ed og other 'free'-licensed library that can do exact decimal arithmetics for us. It should be similar or...
2
by: rong.guo | last post by:
Hello Group, I am having a really weird problem... Can anyone tell the difference between Query 1 and Query 2 below? Why Query 2 excludes '2/28/2005'? Many thanks! create table a...
2
by: Russell Smith | last post by:
Timestamps support infinity. However if appears dates do not. When timestamps are cast to dates, there is no output. Is this an acceptable option or not? Below are a number of examples...
22
by: Mal Ball | last post by:
I hope I have the right forum for this question. I have an existing Windows application which uses a SQL Server database and stored procedures. I am now developing a web application to use the same...
7
by: JJ | last post by:
How do I set one field to have the updated timestamp, and another to have the created timestamp? I want to do this directly from code generated from DB Designer if possible?! JJ
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.