Storing format of date and time

devil_vin

Hey guys! How can I store the following format of date and time in database?
Date=Mon 01th Oct
Time=5 :00pm

buraks78

I prefer unix timestamps. Check out the gmmktime() function.

MarkR

You could either use your database's proper datetime type and use the proper format to get the datetime in /out of it (see your DB's documentation for more details).

MySQL uses ISO-8601 format which is something like 2007-10-01 05:00

You still need to decide which time zone a particular column is in if you do that.

Using unix timestamps stores an instant in time (down to 1 seconds) not a date/time - that's to say, it doesn't store it in any particular time zone.

Consider what you want. You can convert back and forth as often as you like, but bear in mind that it's difficult / impossible to calculate exaclty when a future event will happen, as daylight saving time rules change occasionally due to unpredictable political decisions by specific governments.

Mark

nemonoman

After many years of trial and error, I find using the MySQL column types
DATE
TIME and
DATETIME to be most useful
For selection, MySQL is full of useful date functions that work with its internal datatypes
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

SELECT FROM myTable WHERE YEAR(myDate) ='2007'
SELECT FROM myTable WHERE YEARWEEK(myDate)='200703' #3rd week of year
SELECT FROM myTable WHERE WEEKDAY(myDate)=1 #mondays
SELECT FROM myTable WHERE QUARTER(myDate)=2

Etc. So easy, no conversion,

You can populate any of these with a current date/time like this
SET myColumn=NOW()

Also remember the TIMESTAMP datatype that will automatically beset to NOW() at the time the record is saved, unless explicitly set otherwise. If you have a table with a column myTimestamp set as TIMESTAMP datatype:

INSERT INTO myTable SET id=1
#myTimestamp will automatically be set to NOW()

UPDATE myTable SET resaved=resaved+1 WHERE id=1
#myTimestamp will automatically be set to NOW()

To keep myTimestamp from automatically updated on last save, explicitly state:
UPDATE myTable
SET resaved=resaved+1,
myTimestamp=myTimestamp #Maintain creation timestamp
WHERE id=1