Member Avatar for RazorRamon

I built a quick date time test page. The coding is below followed by its results. I'm entering the raw timestamp into table time because i want to be able to manipulate it once i show it (say if i want to show time and date using the same timestamp). Let me know what i'm doing wrong. Thanks


datetest.php

include 'config2.php';
$dbname = 'time';
$tablename = 'time';
include 'opendb.php';

$name=$_POST['name'];
$date= date("g:ia");
$timestamp=time();


mysql_query ("INSERT INTO $tablename (timestamp,name) 
VALUES ('$timestamp','$name')"); 

$res = mysql_query("SELECT timestamp, name FROM time ORDER BY timestamp DESC;");


while ( $row = mysql_fetch_array($res) ) {
$timestamp = strtotime($row["timestamp"]);
	echo $row["name"] . "<br />";
	echo $row["timestamp"] . "<br />";
	echo date("g:i a F j, Y ", $timestamp) . "<br /><br />";
}

Here are the results. Why does it keep showing me the the same date?

john
1311821078
12:00 am January 1, 1970 

toni
1311820715
12:00 am January 1, 1970 

rafmon
1311820431
12:00 am January 1, 1970
Edited by RazorRamon because: not done
  • 2 Contributors
  • 4 Replies
  • 325 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by RazorRamon

Dani AI

Generated

— the Jan 1, 1970 output is the classic symptom of passing a plain UNIX epoch number through strtotime(). strtotime() expects a textual date/time string and returns FALSE on parse failure; date() then treats that as 0 (the epoch). The quick fix: if you stored time() (seconds since 1970) in the DB, treat it as an integer and feed that integer to date() or to a DateTime object — do not run strtotime() on an epoch number. (php.net)

If you want flexible display (show only time, or show month name, or show "2 hours ago") pick one canonical storage approach and stick with it. Common patterns:

  • store epoch seconds in an INT UNSIGNED column, or
  • store an ISO datetime in UTC (DATETIME or TIMESTAMP) and convert on read. Note: MySQL TIMESTAMP will convert to/from the connection time zone; DATETIME stores the literal value and does not auto-convert. Choose based on range needs (TIMESTAMP historically has the 1970–2038 limitation). ’s suggestion to use DATETIME is valid depending on whether you want automatic TZ conversion. (dev.mysql.com)

To display in the user’s timezone and/or produce “time ago” wording, use PHP’s DateTime API (not mysql_*, and avoid parsing the epoch with strtotime). Example patterns:

// convert stored epoch to user's timezone
$ts = (int)$row['timestamp'];
$dt = new DateTime('now', new DateTimeZone('UTC'));
$dt->setTimestamp($ts);
$dt->setTimezone(new DateTimeZone($userTz));
echo $dt->format('g:i a F j, Y');

// simple "time ago"
function time_ago($ts){
  $now = new DateTime('now', new DateTimeZone('UTC'));
  $past = (new DateTime('now', new DateTimeZone('UTC')))->setTimestamp((int)$ts);
  $diff = $now->diff($past);
  if ($diff->y) return $diff->y.' year'.($diff->y>1?'s':'').' ago';
  if ($diff->m) return $diff->m.' month'.($diff->m>1?'s':'').' ago';
  if ($diff->d) return $diff->d.' day'.($diff->d>1?'s':'').' ago';
  if ($diff->h) return $diff->h.' hour'.($diff->h>1?'s':'').' ago';
  if ($diff->i) return $diff->i.' minute'.($diff->i>1?'s':'').' ago';
  return 'just now';
}

Use DateTime::setTimestamp() / DateTime::diff() for correctness and DST awareness. (php.net)

Security/maintenance notes: the mysql_* API used in earlier posts is deprecated and removed in modern PHP — move to PDO or mysqli with prepared statements. Also avoid using confusing column names (or always quote identifiers with backticks) so future SQL upgrades or parser changes do not bite you. (php.net)

Summary: stop calling strtotime() on numeric epochs, store times in a single canonical form (UTC), convert with DateTime for display, and migrate DB code away from mysql_*.

Recommended Answers

All 4 Replies

Member Avatar for urtrivedi

As far as I know in mysql by default datatime field is stored in
yyyy-mm-dd format. Though if data is in another format then you may use mysql strtodate (some thing like that) function.

I suggest you to directly use mysql current_timestamp function

mysql_query ("INSERT INTO $tablename (timestamp,name) VALUES (current_timestamp,'$name')");

Note: you should also avoid using keywords as column or table name. Here you may change timestamp column to my_time or any other

Edited by urtrivedi because: n/a
Member Avatar for RazorRamon

i tried storing time as int, varchar, and datetime. Datetime seems to be the most strict. I want to be able to convert to month name. Question, why should i change the keywords? i'm sure its for security purposes but can you elaborate?

Member Avatar for urtrivedi

When you are dealing with date and time, always use datatime datatype. Do not use varchar. You will always able to extract data by month, year, week, day whatever you want using certain functions.


I said anywhere you should use some names for columns and tables other than keyword like select, group, from, order, timestamp, date etc.

It is not related to security, but some time certain queries do not work just due to such colnames.
Though it is not mandatory. In mysql You may wrap such names with ``.
like `timestamp`

Edited by urtrivedi because: n/a
Member Avatar for RazorRamon

ok i got it to where it shows me this.

juan
1311871968
4:52 pm July 28, 2011 

george
1311871950
4:52 pm July 28, 2011 

george
1311871488
4:44 pm July 28, 2011

I haven't changed the query to have quotes but this is how i changed it. Now my issue is how do I make it to where it shows the users time zone? Or show how long ago the comment was made?(like on facebook where it shows comment was made 2 hours ago,5 min ago, etc...)

Heres the code.

include 'config2.php';
$dbname = 'time';
$tablename = 'time';
include 'opendb.php';

$name=$_POST['name'];
$date= date("g:ia");
$timestamp=time();


mysql_query ("INSERT INTO $tablename (timestamp,name) 
VALUES ('$timestamp','$name')"); 

$res = mysql_query("SELECT timestamp, name FROM time ORDER BY timestamp DESC LIMIT 0,10;");


while ( $row = mysql_fetch_array($res) ) {
	echo $row["name"] . "<br />";
	echo $row["timestamp"] . "<br />";
	echo date("g:i a F j, Y ",  $row["timestamp"]) . "<br /><br />";
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.