Re: Formatting MySQL's TIMESTAMP

This may become a bit OT, but here goes:

Use MySQL for this. MySQL has (imho) excellent functions for dealing with misc things, for example date and time. Example:

SELECT UNIX_TIMESTAMP(timestamp) AS epoch FROM extable;

This give you the column "timestamp" from the table extable in epoch-format and call the column 'epoch' in the output.

May I also suggest that you format your date with MySQL? This can easily be done with MySQL's DATE_FORMAT function. Example:

SELECT DATE_FORMAT(timestamp,"%Y-%d-%m %H:%i:%S") AS mydate FROM extable;

This would give you the timestamp-column (in output called mydate) in the format: yyyy-mm-dd HH:MM:SS.

Comment on Re: Formatting MySQL's TIMESTAMP Select or Download Code

Replies are listed 'Best First'.
Re: Re: Formatting MySQL's TIMESTAMP by rendler (Pilgrim) on Mar 21, 2002 at 09:03 UTC
What I'm looking for is more on the lines of `"%A %d %B %Y @ %I:%M%p %Z"`.	[reply] [d/l]
Re: Re: Re: Formatting MySQL's TIMESTAMP by dempa (Friar) on Mar 21, 2002 at 09:32 UTC
Well, apart from the timezone that would in DATE_FORMAT syntax equal: `%W %d %M %Y @ %I:%i%p` If you're willing to be a bit database-specific, use UNIX_TIMESTAMP to get the timestamp in epoch-format and then use POSIX::strftime to format your date. `# epoch-date in $epoch use POSIX; my $formatted_date = POSIX::strftime("%A %d %B %Y @ %I:%M%p %Z",localtime($epoch));` [download] If you want to be as non db-specific as possible, take the advice below and forget UNIX_TIMESTAMP. Just select the timestamp-column and use appropriate date-manipulation module to convert it to epoch-format.	[reply] [d/l] [select]
Re: Re: Re: Re: Formatting MySQL's TIMESTAMP by rendler (Pilgrim) on Mar 21, 2002 at 09:52 UTC
Thanks that first option looks good enough for me. Guess I should have really looked at the manual first (lists all the codes).	[reply]


No such thing as a small change
	PerlMonks