How can I manipulate a Sybase datetime string in Perl?

Question

Hey all

I have a datetime field in a Sybase table, and need to use it in Perl for doing some calculations (differences to make sure I'm picking up the records that are at least 'n' mins apart). Now, if I simply do a select <datetime field>, Sybase returns a human readable field which is no use to me. I was looking at some way to convert this into say, an epoch based time, which will allow me to manipulate them easily in Perl.

I could be wrong but I didn't find an existing function for this in Sybase. The closest I got was datediff, which serves my purpose fine. In fact, I am using that as a workaround for now. The problem with datediff is because of the limitation on integer, it's capped at 68 years. I know my code probably won't be around till that time, but why introduce such a time-based limit on my own?

Is there a way to get around this?

Thanks!

Answer 1

If you're ok with doing it in the client, the Time::ParseDate module should be able to do the work for you.

Answer 2

The DateTime modules have served me well when I need to handle date/time calculations.

Use the DBD::Sybase syb_date_fmt() method to control the format, then use DateTime::Format::Strptime to parse your results and generate DateTime objects. Use the DateTime epoch method to get your epoch time, or better yet do your math using the appropriate DateTime::Duration objects.

Answer 3

Thanks Jay, and daotoad!

Didn't know about the Time::ParseDate module. I did know about the syb_date_fmt() and then converting it to epoch time, but I like the simplicity of Time::ParseDate :-)

Answer 4

If you can't get Sybase and Perl to cooperate to give you a genuine DateTime object or something similar (like Time::Piece), then the way I usually handle such things is to try to convince the database to give me ISO standard date format (YYYY-MM-DDTHH:MI:SS) and convert from there.