SELECT SQL_CALC_FOUND_ROWS wp_posts.*, (
SELECT SUM(rooms_available) rooms_available FROM (SELECT IFNULL(SUM(room_count), 0) rooms_available, availabilities.accommodation_id FROM wp_byt_accommodation_vacancies availabilities INNER JOIN (
select d.the_date from
(
select adddate('2024-12-28',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) the_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
having the_date between '2024-12-28' and '2026-12-28'
ORDER BY the_date
) d
where DATE(d.the_date) >= DATE(CURDATE())
group by d.the_date
order by d.the_date
) possible_dates ON (possible_dates.the_date >= DATE(availabilities.start_date) AND possible_dates.the_date < DATE(availabilities.end_date)) WHERE 1=1 AND ('2024-12-28' BETWEEN start_date AND end_date OR start_date <= '2026-12-28') AND end_date >= '2024-12-28' GROUP BY possible_dates.the_date, availabilities.accommodation_id ) as ra WHERE (accommodation_id = wpml_translations_default.element_id OR accommodation_id = wpml_translations.element_id) ) rooms_available , (
SELECT IFNULL(SUM(rooms_booked), 0) rooms_booked FROM ((SELECT IFNULL(SUM(bookings.room_count), 0) rooms_booked, bookings.accommodation_id, possible_dates.the_date FROM wp_byt_accommodation_bookings bookings INNER JOIN (
select d.the_date from
(
select adddate('2024-12-28',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) the_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4
having the_date between '2024-12-28' and '2026-12-28'
ORDER BY the_date
) d
where DATE(d.the_date) >= DATE(CURDATE())
group by d.the_date
order by d.the_date
) possible_dates ON (possible_dates.the_date >= DATE(bookings.date_from) AND possible_dates.the_date < DATE(bookings.date_to)) WHERE 1=1 GROUP BY possible_dates.the_date, accommodation_id ) as rb, (SELECT MIN(start_date) min_start_date, MAX(end_date) max_end_date, accommodation_id FROM wp_byt_accommodation_vacancies GROUP BY accommodation_id) min_max_available_dates ) WHERE (rb.accommodation_id = wpml_translations_default.element_id AND min_max_available_dates.accommodation_id = wpml_translations_default.element_id) OR (rb.accommodation_id = wpml_translations.element_id AND min_max_available_dates.accommodation_id = wpml_translations.element_id) AND rb.the_date >= min_max_available_dates.min_start_date AND rb.the_date < min_max_available_dates.max_end_date ) rooms_booked , IFNULL((SELECT price_meta2.meta_value + 0 FROM wp_postmeta price_meta2 WHERE price_meta2.post_id=wpml_translations_default.element_id AND price_meta2.meta_key='_accommodation_min_price:2024-12-28:2026-12-28' LIMIT 1), 0) accommodation_price , IFNULL((SELECT price_meta3.meta_value + 0 FROM wp_postmeta price_meta3 WHERE price_meta3.post_id=wpml_translations_default.element_id AND price_meta3.meta_key='accommodation_static_from_price' LIMIT 1), 0) accommodation_static_price
FROM wp_posts JOIN wp_icl_translations wpml_translations
ON wp_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) LEFT JOIN wp_icl_translations wpml_translations_default ON wpml_translations_default.trid = wpml_translations.trid AND (wpml_translations_default.source_language_code IS NULL OR wpml_translations.source_language_code IS NULL)
WHERE 1=1 AND wp_posts.post_type = 'accommodation' AND ((wp_posts.post_status = 'publish')) AND ( ( ( wpml_translations.language_code = 'en' OR 0 ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_global_styles','wp_navigation','location','review','extra_item','room_type','accommodation','tour','cabin_type','cruise','car_rental','wpcf7_contact_form','cookielawinfo' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_global_styles','wp_navigation','location','review','extra_item','room_type','accommodation','tour','cabin_type','cruise','car_rental','wpcf7_contact_form','cookielawinfo' ) )
GROUP BY wp_posts.ID HAVING 1=1 AND rooms_available > rooms_booked AND rooms_available >= 1
ORDER BY accommodation_price ASC
LIMIT 0, 12
Almost on the water’s edge, this small family complex overlooks the sea and the countryside. It is situated on the edge of the town of Sitia, separated from the sandy beach by a pedestrian area. All apartments and studios offer spacious and comfortable accommodation, the interior design reflecting the soft colours of the Aegean. Some have sea view and some garden view.
Set in a slightly elevated position, just 200m from Kouremenos Beach, these 3 villas are surrounded by a large garden with lawns and olive trees and enjoy wonderful, uninterrupted views of the countryside and down to the sea (villa C’ has part sea-view).
This most attractive complex is built on two floors and is surrounded by impressive gardens, complete with a delightful waterfall feature.
It is separated from the sandy beach of Sitia by the Sitia-Palekastro road and is a mere 5 minute walk to the town centre.
Situated just above the small fishing harbour of Sitia, approx. 160 metres from the town centre and approx. 120 metres from the sand and pebble beach of Sitia, this family owned and run hotel is composed of two buildings, one newly built and the other newly refurbished, both in a modern /classic style.
Itanos Hotel is situated in the centre of the town of Sitia, overlooking the small fishing harbour, approx. 280 metres from the sand and pebble beach of Sitia.
These apartments are situated right by the long beach of Kouremenos, in a very quiet and peaceful area. The two complex are built in an area of 3800 sq.m. The apartments are surrounded by olive trees and enjoy wonderful views of the sea over the garden. The tranquil environment and the crystal-clear sea are the main characteristics of this area, which make the complex ideal for visitors seeking peace, quiet and relaxation next to the sea.
Hidden in a quiet lush olive grove on the outskirts of the small traditional village of Agathias, approx. 1400 metres from the village of Palekastro and approx. 1200 metres from the sand and pebble beach of Chiona and the small traditional fish tavernas, Aeolos Studios are ideal for those wishing for peaceful and restful holidays in the Cretan countryside.
Set on a hillside, enjoying an uninterrupted view over the magnificent bay of Kouremenos, as well as the lush olive groves that fringe the shore, these apartments are approx. 1400 metres from the long, sand and pebble beach of Kouremenos, and approx. 3 km from the village of Palekastro.
These three villas, built in traditional Cretan style, are situated on the slope of a hill, in a very quiet and peaceful area, with a spectacular view of the long, sand and pebble beach of Kouremenos and the surrounding mountains.
This bungalow-style hotel is set in the heart of the Cretan countryside, surrounded by lush olive groves, plants and flowers, offering peace and tranquility.
This small family hotel with its inviting pool is ideal for those wishing to be close to village life and facilities but also not far from the sea and countryside.
A cluster of comfortable apartments set in the unspoilt Cretan countryside and 3.000 sq.m of lush green olive grove, approx. 70 metres from the sand and pebble beach of Kouremenos, Grandes Apts (named after the small islands of Kouremenos Bay), are the perfect choice for those who want to relax and enjoy the sea and sun in peaceful surroundings.
Cookies are small files which are sent to your browser and stored on your computer’s hard disc. If you have registered with Portobelis Crete Holidays, then your computer will store an identifying code in the cookie which means you do not need to type in your email address each time you return to the site. Other than this, we do not store your password or any other information in the cookie, or use it for any other purpose. Our third-party advertiser may place or recognize a unique cookie on your browser.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
