Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

My SQL timestamp returns a timestamp in the past? Incorrect timestamp?

Posted on 2009-02-17
7
Medium Priority
?
254 Views
Last Modified: 2012-05-06
Hi there,

If I run the command below in php my admin:
SELECT unix_timestamp( '2009-04-03' );

I get the following timestamp: 1238713200
If I convert this timestamp to a date it is: Thu, 02 Apr 2009 23:00:00 GMT

This is obviously incorrect? I need to run the above and get the timestamp for 2009-04-03 00:00:00. Am I doing something wrong?

Cheers
0
Comment
Question by:jusmeig
  • 4
  • 2
7 Comments
 
LVL 21

Assisted Solution

by:K V
K V earned 75 total points
ID: 23659188
Is this what you do?
SELECT unix_timestamp( '2009-04-03' );
select from_unixtime(1238731200);

0
 

Author Comment

by:jusmeig
ID: 23659271
If I do what you say, this is what I get:
SELECT unix_timestamp( '2009-04-03' ); - 1238713200
select from_unixtime(1238713200); - 2009-04-03 00:00:00

But if I convert the timestamps here using: http://www.onlineconversion.com/unix_time.htm
I get: Thu, 02 Apr 2009 23:00:00 GMT

The timestamp php generates for 2009-04-03 = 1238716800

So when I try to do a comparison there is no match, and my program breaks! Is it the PHP timezone that is wrong?



0
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 300 total points
ID: 23659275
It loses hours due to conventions for local time zone changes,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:jusmeig
ID: 23659617
Hi there,

I saved my dates as a date.
I guess I could just compare the value of the date and my php date as a string match?
IE match yyyy-mm-dd as a string match, this will get me around time zones etc?

J
0
 
LVL 27

Assisted Solution

by:Lukasz Chmielewski
Lukasz Chmielewski earned 300 total points
ID: 23659767
yes, but comparing like this will not tell you that some is smaller or larger (in time). if it does match, than it does match.

try with UTC_TIMESTAMP in mysql
0
 

Author Comment

by:jusmeig
ID: 23660404
Not interested in the time, just the date.
Roads_Road u get some of the beans.....as I guess you mentioned what was wrong....but the workaround was mine :)
0
 

Author Closing Comment

by:jusmeig
ID: 31547754
Problem was identified, I found the workaround but the guys certainly helped!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question